Planned improvements to database handling

[YourOrdinaryCat]

I'm making this issue to get some feedback regarding the way we currently handle indexing and databases. Currently we use an Entity Framework Core (EFC) backend with foreground change tracking, checking for changes from time to time and when certain events are triggered. Whenever we detect new media, we insert it into the DB and add it to the respective collection.

This system has gotten mixed feedback from end users. To make matters worse, it causes an increasing amount of issues:

• EFC makes app startup in debug builds slow down to a crawl, and EFC 5 won't be made available to UWP due to it needing .NET Standard 2.1. Inserting big amounts of items into the DB can also be quite slow
• Foreground change tracking means we can't get a full list of library changes, which means we have to trigger full library crawls to add new media
• Because of the way we handle inserting data into the DB, we currently cannot insert media in the background, it must be done when the app is running

Here's the list of things I currently want to achieve:

• Implement background change tracking (resolved in Improvements to change tracking and indexing #233)
• Instead of inserting to the DB and the media collections at the same time, insert everything into DB first, and when we're done, get data from there
• Move away from EFC (not necessary but it could certainly improve things in the long run) (Massive backend/indexing improvements #159)

With that said, do post your feedback here! If you're a dev, feel free to suggest improvements to our current tooling. If you're not, make a separate issue and link it here. As always, make sure to provide concrete feedback (for example, "songs from foo don't show up if they're in a folder called bar").

[itsWindows11]

Are you planning on making the indexer only adding/removing items when needed instead of indexing and upserting and re-adding the whole list again?

[YourOrdinaryCat]

Only indexing and upserting when needed, but the collections within the app would be reset, it's more reliable to clear then insert entire ranges with ObservableRangeCollection. AdvancedCollectionView takes a long time to properly handle vector change events.