EntityFramework Core Database Migration in Azure DevOps Pipeline

Thanks for pointing this out, Tomek.

I have not used stored procedures in my projects thus cannot comment on that experience at the moment.

Your workaround will definitely work but then it relies on a separate application host in CD pipeline and also the changes will not be visible directly without looking into C# code. Another workaround I can think of is that you could directly keep your stored procedure scripts in separate folders and concatenate them with these generated scripts to form your migration SQL scripts. But obviously you won't be able to easily migrate up or down compared with your approach unless we implement the script versions management function, which itself can be complex. Thus overall, I agree that your approach is a good approach to handle these problems. 

If you want more advance features, there are also many open source and commercial software that can probably manage the schema migrations better if your project is complex. 

This approach has limitations. If you use migrationBuilder.Sql(string) method e.g. to update stored procedure in a migration migrations.sql file will be syntactically incorrect and some changes will be not applied. After attempting several workarounds I dropped this method completely and instead of generating sql script I use a small command line app that has about 5 lines and executes context.Database.Migrate() method. It does not crash on stored procedures and logging is better than with sqlcmd. 

It depends on:

• Whether your portals and functions share the same database?
• Whether you are using a single project for storing migration C# scripts.

For my case, there is only one website portal and one shared migration project (where all migration C# scripts locate); thus I just used those two projects.

In Entity framework core migration script generator task
which path should be given for Main project path and DB context
as we have 6 portals and 15 function apps in one Repo

how can we rollback the Migration in case of failure