How do I force Entity Framework to mark a particular migration as having been applied?

Question

I'm using Entity Framework 5 in a project, and I've got Migrations enabled.

Here's the scenario:

A new developer (dev1) comes on and builds the project from source. There are existing migrations since previous developers had been working on the project in the past.

When that developer runs the ASP.NET MVC project for the first time, the database builds automatically, and no errors appear.

After that, however, a different developer (dev2) adds a new migration. When Dev1 tries to run Update-Database, all of the previous migrations are attempted to run. But they have already been applied, since they were part of the initial model as Dev1 saw it. This often results in a schema error since it's trying to apply a schema change that already exists.

So, optimally, it would be great to just "fast forward" the local database to the current migration. But I don't know of a way to do that. Alternatively, is there some other way to initialize the database so that I can apply all the migrations during initialization?

Llovera

When your developer runs the project for the first time are they running all the migrations in the project? or are they adding a new migration and just running that? – Snap

When they run the project for the first time, it builds the database with the current model. If they run migrations at that time, it often still results in an error. – Llovera

Are you using automatic migrations, code first migrations or both? – Snap

Code First Migrations. We don't use Automatic Migrations though, we add them all manually with Add-Migration – Llovera

Answer 1

I figured out a hack.

Run Update-Database -Script

Pick out all the migrations that have already been run

INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES

Open Sql Server Management Studio, and run those sql statements manually.

New migrations should work fine.

Llovera

If you have already made changes to the model classes, this command will fail since there are more changes. Use your revision control system to go back to the branch where Initial was created and run Update-Database -Script to avoid the error. – Varese

as Update-Database -Script does not exist in ef core i use dotnet ef migrations script --idempotent --output "script.sql" --context ApplicationDbContext – Ansilme

I've achieved the same result via manual editing of the __EFMigrationsHistory table in Visual Studio - SQL Server Object Explorer - __EFMigrationsHistory - View Data. – Zirconium

@Zirconium can you elaborate on what you added to the table? Specifically, what am I supposed to put into column Product Version? – Fonda

@KolobCanyon I just left required migrations in the table. I didn't change the ProductVersion, it is "5.0.1" in all the rows. – Zirconium

Answer 2

Entity Framework migrations in a team environment can be tricky. Especially when combined with source control. It sounds like you are running into issues with different code-first migrations being run against different dev databases.

All the migrations that have been run against a particular database are stored in the __MigrationHistory table. If you have a migration file in your project and EF doesn't see it in the __MigrationHistory table it's going to try and execute it when you run Update-Database. If you trick EF into thinking that it's already applied these migration files by inserting the records into MigrationHistory it will break one of the nicest features of EF code first. You won't be able to role back your migrations using update-database -TargetMigration.

If each dev has their own set of migrations and it's possible for them to have overlapping changes this can result in all sorts of sql errors when you run update-database.

My solution to this has been to ignore all migration files having to do with active development (since they tend to be tweeked a lot). When a dev gets a new change set they just create their own local migrations.

Once I am ready to release a new feature to production I roll all those changes into one big migration and I usually name it after the version number that I am incrementing my app to. I check these migration files into source control. These act as my master migration files to bring any new database up to date with production.

When I make a new one of these master migrations all devs role back their local changes to the last major version, delete the ones they don't need anymore (because they are covered in master migration), then run update-database.

Snap

We've actually come to a similar solution. We never check in migrations anymore. Each developer just has them locally, and deletes them in the csproj file before they commit their changes. We then just generate a migration (similar to yours) that exists to move staging and production databases to the current version. – Llovera

Hello, I 'm trying to solve the same problem, 7 years later. How did this work out for you? I can see some problems in this approach, considering CI, Deployment, and using migration-based data seeding in an application. – Kacerek

Answer 3

I took Doug's technique and created a DatabaseInitializer that automates it.

Just use

 Database.SetInitializer(new CreateDbWithMigrationHistoryIfNotExists<EntityContext, Configuration>());

In your DbContext and it will create a new DB if one does not exist and update the migration history table to include all existing migrations.

Here is the class:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Infrastructure;
using System.Data.Entity.Migrations.Model;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Xml.Linq;

namespace EfStuff
{
    public class CreateDbWithMigrationHistoryIfNotExists<TContext, TMigrationsConfiguration> :
        IDatabaseInitializer<TContext>
        where TContext : DbContext
        where TMigrationsConfiguration : DbMigrationsConfiguration<TContext>
    {
        private readonly Regex _pattern = new Regex("ProviderManifestToken=\"([^\"]*)\"");
        private readonly TMigrationsConfiguration _config;

        public CreateDbWithMigrationHistoryIfNotExists()
        {
            _config = Activator.CreateInstance<TMigrationsConfiguration>();
        }

        public void InitializeDatabase(TContext context)
        {
            if (context.Database.Exists()) return;
            context.Database.Create();

            var operations = GetInsertHistoryOperations();
            if (!operations.Any()) return;
            var providerManifestToken = GetProviderManifestToken(operations.First().Model);
            var sqlGenerator = _config.GetSqlGenerator(GetProviderInvariantName(context.Database.Connection));
            var statements = sqlGenerator.Generate(operations, providerManifestToken);
            statements.ToList().ForEach(x => context.Database.ExecuteSqlCommand(x.Sql));
        }

        private IList<InsertHistoryOperation> GetInsertHistoryOperations()
        {
            return
                _config.MigrationsAssembly.GetTypes()
                       .Where(x => typeof (DbMigration).IsAssignableFrom(x))
                       .Select(migration => (IMigrationMetadata) Activator.CreateInstance(migration))
                       .Select(metadata => new InsertHistoryOperation("__MigrationHistory", metadata.Id,
                                                                      Convert.FromBase64String(metadata.Target)))
                       .ToList();
        }

        private string GetProviderManifestToken(byte[] model)
        {
            var targetDoc = Decompress(model);
            return _pattern.Match(targetDoc.ToString()).Groups[1].Value;
        }

        private static XDocument Decompress(byte[] bytes)
        {
            using (var memoryStream = new MemoryStream(bytes))
            {
                using (var gzipStream = new GZipStream(memoryStream, CompressionMode.Decompress))
                {
                    return XDocument.Load(gzipStream);
                }
            }
        }

        private static string GetProviderInvariantName(DbConnection connection)
        {
            var type = DbProviderServices.GetProviderFactory(connection).GetType();
            var assemblyName = new AssemblyName(type.Assembly.FullName);
            foreach (DataRow providerRow in (InternalDataCollectionBase) DbProviderFactories.GetFactoryClasses().Rows)
            {
                var typeName = (string) providerRow[3];
                var rowProviderFactoryAssemblyName = (AssemblyName) null;
                Type.GetType(typeName, (a =>
                                            {
                                                rowProviderFactoryAssemblyName = a;
                                                return (Assembly) null;
                                            }), ((_, __, ___) => (Type) null));
                if (rowProviderFactoryAssemblyName != null)
                {
                    if (string.Equals(assemblyName.Name, rowProviderFactoryAssemblyName.Name,
                                      StringComparison.OrdinalIgnoreCase))
                    {
                        if (DbProviderFactories.GetFactory(providerRow).GetType().Equals(type))
                            return (string) providerRow[2];
                    }
                }
            }
            throw new Exception("couldn't get the provider invariant name");
        }
    }
}

Thema

I've been attempting a similar approach but they have removed InsertHistoryOperation from EF6, have you upgraded your approach to EF6 yet? – Argus

Answer 4

Base on the Doug's answer, if you use ef core version Update-Database -Script will throw A parameter cannot be found that matches parameter name 'Script'..

But there is good replacement in .net core cli (alternative for VS tools):

dotnet ef migrations script --idempotent

If you do now have dotnet ef cli, install them first by:

dotnet tool install --global dotnet-ef

Then you can copy generated sql and cherry-pick what you need directly to db.

Source: https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/managing?tabs=vs#resetting-all-migrations

Moonier

Answer 5

EF Core Solution:

public static void MarkCompleted(string migrationName)
{
    var versionStr = GetEfVersion();

    var ctx = new DbContextCreator(); // your ctx
    var existing = ctx.Database.GetAppliedMigrations();

    if (! existing.Contains(migrationName))
    {
        FormattableString sql = @$"INSERT INTO [dbo].[__EFMigrationsHistory] 
([MigrationId] ,[ProductVersion])  
VALUES ({migrationName},{versionStr})";

        ctx.Database.ExecuteSql(sql);
    }
}

public static string GetEfVersion()
{    
    // The EF Core version, as obtained from the
    // AssemblyInformationalVersionAttribute of the EF Core assembly.
    var asm = typeof(DbContext).Assembly;

    var attr = asm.GetCustomAttribute<AssemblyInformationalVersionAttribute>()!;
    var versionStr = attr.InformationalVersion;

    return versionStr;
}

Sire