Adding 'Go' Statements to Entity Framework Migrations

Adding 'GO' statements to Entity Framework migrations

In order to change the SQL Generated by entity framework migrations you can create a new SqlServerMigrationSqlGenerator

We have done this to add a GO statement before and after the migration history:

public  class MigrationScriptBuilder: SqlServerMigrationSqlGenerator
{
    protected override void Generate(System.Data.Entity.Migrations.Model.InsertHistoryOperation insertHistoryOperation)
    {
        Statement("GO");

        base.Generate(insertHistoryOperation);

        Statement("GO");

    }
}

then add in the Configuration constructor (in the Migrations folder of the project where you DbContext is) so that it uses this new sql generator:

[...]
internal sealed class Configuration : DbMigrationsConfiguration<PMA.Dal.PmaContext>
{
    public Configuration()
    {
        SetSqlGenerator("System.Data.SqlClient", new MigrationScriptBuilder());
        AutomaticMigrationsEnabled = false;
    }
[...]

So now when you generate a script using the -Script tag, you can see that the insert into [__MigrationHistory] is surrounded by GO

Alternatively in your implementation of SqlServerMigrationSqlGenerator you can override any part of the script generation, the InsertHistoryOperation was suitable for us.

Entity Framework Migrations: Including Go statement only in -Script output

internal sealed class Configuration : DbMigrationsConfiguration<Context>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        const string providerInvariantName = "System.Data.SqlClient";
        SetSqlGenerator(providerInvariantName, new BatchingMigrationSqlGenerator(GetSqlGenerator(providerInvariantName)));
    }

    protected override void Seed(Context context)
    {
    }

}

internal class BatchingMigrationSqlGenerator : MigrationSqlGenerator
{
    private readonly MigrationSqlGenerator migrationSqlGenerator;

    public BatchingMigrationSqlGenerator(MigrationSqlGenerator migrationSqlGenerator)
    {
        this.migrationSqlGenerator = migrationSqlGenerator;
    }

    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
        var migrationStatements = migrationSqlGenerator.Generate(migrationOperations, providerManifestToken).ToArray();
        foreach (var migrationStatement in migrationStatements)
        {
            migrationStatement.BatchTerminator = "GO";
        }
        return migrationStatements;
    }
}

Entity Framework - how to create sql script with GO statements

You can override the script generation behavior by creating a wrapper class around the SqlServerMigrationSqlGenerator class. This class contains an overloaded Generate() method which takes in arguments that represent the type of script block it's generating. You can override these methods to add "GO" statements before starting new script blocks.

public class ProdMigrationScriptBuilder : SqlServerMigrationSqlGenerator
{
    protected override void Generate(HistoryOperation insertHistoryOperation)
    {
        Statement("GO");
        base.Generate(insertHistoryOperation);
        Statement("GO");
    }

    protected override void Generate(CreateProcedureOperation createProcedureOperation)
    {
        Statement("GO");
        base.Generate(createProcedureOperation);
    }

    protected override void Generate(AlterProcedureOperation alterProcedureOperation)
    {
        Statement("GO");
        base.Generate(alterProcedureOperation);
    }

    protected override void Generate(SqlOperation sqlOperation)
    {
        Statement("GO");
        base.Generate(sqlOperation);
    }
}

You will also need to set this class as the Sql Generator in your Configuration class constructor.

    public Configuration()
    {
        AutomaticMigrationsEnabled = false;

       // Add back this line when creating script files for production migration...
       SetSqlGenerator("System.Data.SqlClient", new ProdMigrationScriptBuilder());

    }

One gotcha to this approach is that while it works great for creating re-usable script files for SQL Server Enterprise Manager, the GO statements do not work when executing migrations locally. You can comment out the SetSqlGenerator line when working locally, then simply add it back when you are ready to create your deployment scripts.

Using multiple SQL statements in EF migration - how to use GO to run in SSMS?

I found an odd fix to this problem:

GO
--any arbitrary comment here
GO

My guess is that EF will remove GO from the end of a Sql() call:

Sql(@"
--actual SQL here
GO");

Including the arbitrary comment seems to mean that only the final GO is removed, but the initial one is kept. This works for me regardless of whether I run the migration through EF or generate a script to run in SSMS.

Entity Framework Data Migration

A MigrationBuilder is a way to express in a fluent, C#-friendly fashion the SQL code that will be executed during the migration itself. Even if the code you write and the "intermediate files" generated by the migration tool look as C#, at the end the migration becomes pure SQL that neither knows nor can communicate with your application.

Therefore, if you need to process existing data, all your logic must happen DB-side and must be expressed as operations on the MigrationBuilder. Of course in the Up and Down methods of the migration you can leverage all the power of C# to build the raw SQL statements to be provided to the migrationBuilder.Sql(...). Also, consider that the whole migration is executed in a transaction and that a "volatile" stored procedure can turn out to be useful to extract a complex migration logic.

Add data using migration Entity Framework Core without specifying the id

I found a way to do an insert during migration:

        migrationBuilder.InsertData(
            table: "TypeNote",
            columns: new[] { "Name" },
            values: new object[,]
            {
                { "Test" },
                { "Test1" }
        });

The fact is that I wanted to access dbContext inside the migration. Which is impossible because DB is updating.

Execute custom SQL script as part of Entity Framework migration

Moving a populated, non-nullable column

Get Entity Framework to create the base migration and then enhance the output.

Some example code that moves an EmailAddress field from OldTable to NewTable (MS SQL Server):

migrationBuilder.AddColumn<string>(
    name: "EmailAddress",
    table: "NewTable",
    defaultValue: "");

migrationBuilder.Sql("UPDATE NewTable SET NewTable.EmailAddress = OldTable.EmailAddress FROM NewTable JOIN OldTable ON NewTable.Id = OldTable.NewTableId");

migrationBuilder.AlterColumn<string>(
    name: "EmailAddress",
    table: "NewTable",
    nullable: false,
    defaultValue: "");

migrationBuilder.DropColumn(
    name: "EmailAddress",
    table: "OldTable");

Remember, this needs to happen for Up() and Down(), except Down() undoes the operation.

How does Entity Framework know which migration to add?

Seems you are using entity framework migrations and got confused how it works. Here is the explanations:

Question: But the thing I don't understand how does it know which model I want for my table?

• If you look into your project folder there is the directory
  Migrations. Inside it all the migrations history logs written
  into.When we made any changes on data model, EF Core compares the current model against a snapshot of the old model to determine the
  differences, and generates migration source files; the files can be
  
  tracked in your project's source control like any other source file.
• Once a new migration has been generated, it can be applied to a database in various ways. EF Core records all applied migrations in a
  special history table, allowing it to know which migrations have been
  applied and which haven't

Question: If I would have 2 models before I did any migrations which model would get chosen?

• As said earlier, as it keep track previous migrations history, so in your old model it compares the differences and overrite latest
  changes that were not written on older files. This is how it works.

Hope above explanations guided you accordingly and redeem your confusions. You can also have a look on official documents here

How to add Extra properties to migration in .net core razor pages without deleting the previous migration or database in EF core

Updating with Code First Approach takes little extra care w.r.t Database First Approach.

Initially, you must enable migrations for the project

Enable-Migrations -ContextTypeName DBNAME.StoreContext

This will create a migration folder with the Configuration.cs file.
Next, we need to make our migration run. This is where you need to add an extra step to an existing database. If we create a migration now, it will attempt to add all our entities to the database. This will not work because other tables already exist in the database, so we need to create an initial blank migration and then later we will be able to add a migration for any new changes. To create blank migration execute this command

Add-Migration InitialCreate -IgnoreChanges

The key part of this command is the -IgnoreChanges flag, which ensures that migration is created that effectively does nothing. Running it will add an entry to the migrations table in the database, thus creating a snapshot of its original schema.

Next, run the update-database command to update the existing database with the initial migration. A new migrations table will now have been created in the DBNAME database.

Following this, you can add your properties, and after that execute this statement
Add-Migration add_classname_propertyname

After that run, the update-database command and you are good to go.

---

Related Topics