EfCore.TestSupport VS DbUp

If you have complex ef queries / functions mocking repository doesn't really make Sense. Your core application live in entity framework, you must test it. Author of best book about entity framework wrote lot blog posts about it (https://www.thereformedprogrammer.net/new-features-for-unit-testing-your-entity-framework-core-5-code/) maybe you can write extension methods on queryable and test it?

I think it would be fine for a small app. For larger apps: we currently use DbUp to run SQL scripts on deploy (in a seperate job, before the web app deploys) + https://github.com/JonPSmith/EfCore.TestSupport to run a unit test to ensure the EF Core schema matches the database to prevent errors at runtime.

I am not super familiar with SqlDataProvider, but its generally purpose is to interact with the DB at runtime, and not to migrate sql databases. I would probably use some ddl migration tool like DbUp, https://github.com/DbUp/DbUp, to handle table creation.

I've used a simple migration tool called DbUp https://github.com/DbUp/DbUp

Yea - it usually takes some diligence to maintain sprocs in a nice way. When my teams have used dapper in a production environment/application we usually combine it with something like dbup which lets us source control all of the objects/up/down scripts. It's like EF migrations but works with simple scripts.

Check out DbUp This is what we use and created a custom app that gets invoked within our pipeline