access_time 2 years ago languageEnglish
more_vert

EntityFramework Core Database Migration in Azure DevOps Pipeline

visibility 5,387 comment 6
EntityFramework Core is a commonly used framework in .NET Core applications to access and manage data in a database like SQL Server. When using code-first approach, migration scripts are generated using CLI tool and then database update command is used to apply the changes to databases. To perform ...
info Last modified by Administrator 12 months ago
thumb_up 0

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

comment Comments
5 months ago link more_vert
Raymond Raymond
articleArticles 546
imageDiagrams 49
codeCode 2
chat_bubble_outlineThreads 8
commentComments 250
loyaltyKontext Points 6005
account_circleProfile
#1681 Re: 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. 

format_quote

person Tomek access_time 5 months ago
Re: EntityFramework Core Database Migration in Azure DevOps Pipeline

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. 

5 months ago link more_vert
T Tomek
articleArticles 0
imageDiagrams 0
codeCode 0
chat_bubble_outlineThreads 0
commentComments 1
loyaltyKontext Points 1
#1680 Re: EntityFramework Core Database Migration in Azure DevOps Pipeline

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. 

10 months ago link more_vert
Raymond Raymond
articleArticles 546
imageDiagrams 49
codeCode 2
chat_bubble_outlineThreads 8
commentComments 250
loyaltyKontext Points 6005
account_circleProfile
#1620 Re: EntityFramework Core Database Migration in Azure DevOps Pipeline

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.

format_quote

person Uday access_time 10 months ago
Re: EntityFramework Core Database Migration in Azure DevOps Pipeline

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

10 months ago link more_vert
Raymond Raymond
articleArticles 546
imageDiagrams 49
codeCode 2
chat_bubble_outlineThreads 8
commentComments 250
loyaltyKontext Points 6005
account_circleProfile
#1618 Re: EntityFramework Core Database Migration in Azure DevOps Pipeline

There are several things you can do:

1) Backup your database before migration so that you can restore if any failure.

2) You can use dotnet ef migrations script to generate roll back script too.

For example, the following command will generate scripts to roll back from a migration named LatestMigrationName to PreviousMigrationName.

dotnet ef migrations script LatestMigrationName PreviousMigrationName


format_quote

person Uday access_time 10 months ago
Re: EntityFramework Core Database Migration in Azure DevOps Pipeline

how can we rollback the Migration in case of failure

recommendMore from Kontext