Efcore: how to generate a update sql script?

[lynxliu]

we are migrating our system to dot net core and using ef core to manage our db. sometime we need reorganize our model like add some new property to business model. so i often need to update our db by ef core update command. for developer that is enough. but we have many db server in different place. maintainer hope can run a sql script instead using command ef "update" .how can i get the migrate sql script? or ef core will support this feature in future? thanks.

[Muchiachio]

Try dotnet ef migrations script --help, it can give you migration scripts for various migration ranges.

[gercobrandwijk]

The problem with that command is that you have to define from which migration till which migration you want an SQL script. This means that you need to look in your migration history and find the name of the latest migration. This was not needed in EF6/7, there the update-database -script command created a script for the latest migration.

To mimic this behavior till EF Core has a better -script command I created a small Powershell scripts which finds the latest migration for you.

param([string]$name)

if(-not($name)) { Throw "You must supply a name for the migration" }

$migrations = Get-ChildItem 'PATH_MIGRATION_PROJECT\Migrations' -Filter '*_*' | sort-object name -Descending

$latestMigration = $migrations[0].ToString().Split('.')[0]

$outputFile = "../PATH_SCRIPT_OUTPUT_PROJECT/Scripts/" + ("{0:yyyyMMddHHmmss}" -f (Get-Date)) + "_" + $name + ".sql"

cd GoBright.Signage.Tenant.Database

dotnet clean
dotnet build --no-incremental
dotnet ef migrations add $name --context DatabaseContext --startup-project ../NAME_OF_START_PROJECT
dotnet ef migrations script $latestMigration $name --context DatabaseContext --startup-project ../NAME_OF_START_PROJECT --output $outputFile

cd ..

Perhaps this is usefull for someone else too. Don't forget to replace the three variables:

• PATH_MIGRATION_PROJECT (folder name/location of the project where the migrations must be stored)
• PATH_SCRIPT_OUTPUT_PROJECT (folder name/location of the project where the script files must be stored)
• NAME_OF_START_PROJECT (folder name/location of the project where the DatabaseContext/ConnectionString for the context is defined)

[ajcvickers]

@lynxliu Sounds like you need to generate an idempotent script

[ErikEJ]

EF Core Power Tools can do that for you (generate an idempotent script)

[ajcvickers]

Love the Power Tools!

[gercobrandwijk]

@ajcvickers This will create a huge script containing all the migrations right?

[ajcvickers]

Yes