Entity Framework Code-First Migration Fails with SQL Server

Question

Using Entity Framework 6 and SQL Server, I get the following error when EF is doing the migration thing:

Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.

Everything works perfectly fine with MySQL.

There have been no changes. Running another "Add-Migration" results in empty Up()/Down() methods:

public partial class Two : DbMigration
{
    public override void Up()
    {
    }

    public override void Down()
    {
    }
}

The DbMigrationsConfiguration class:

internal sealed class Configuration : DbMigrationsConfiguration<MyDBContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("System.Data.SqlClient", new SqlServerMigrationSqlGenerator());
    }

    protected override void Seed(MyDBContext context)
    {
        ...
    }
}

The DbContext.OnModelCreating:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyDBContext, Configuration>());

        ...
    }

Also, examining the __MigrationHistory it seems like everything is in order.

What am I doing wrong?

An update: Turning on automatic migrations temporarily to see what changes it makes, it changes all the time column precision/scale numbers from 16/7 to 8/0. I don't understand why it creates them with one precision and then wants them to have a different one later on though...

Update 2, the queries:

Here are the queries that create the relevant tables:

CREATE TABLE [dbo].[Instruments] (
[ID] [int] NOT NULL IDENTITY,
[Symbol] [nvarchar](255),
[UnderlyingSymbol] [nvarchar](255),
[Name] [nvarchar](255),
[PrimaryExchangeID] [int],
[ExchangeID] [int],
[Type] [int] NOT NULL,
[Multiplier] [int],
[Expiration] [datetime],
[OptionType] [int],
[Strike] [decimal](16, 8),
[Currency] [nvarchar](25),
[MinTick] [decimal](16, 8),
[Industry] [nvarchar](255),
[Category] [nvarchar](255),
[Subcategory] [nvarchar](255),
[IsContinuousFuture] [bit] NOT NULL,
[ValidExchanges] [varchar](max),
[DatasourceID] [int] NOT NULL,
[ContinuousFutureID] [int],
[SessionsSource] [int] NOT NULL,
[SessionTemplateID] [int],
[DatasourceSymbol] [nvarchar](255),
CONSTRAINT [PK_dbo.Instruments] PRIMARY KEY ([ID])
)

CREATE TABLE [dbo].[exchangesessions] ( 
[ID] [int] NOT NULL IDENTITY,
[OpeningTime] [time](3) NOT NULL,
[ClosingTime] [time](3) NOT NULL,
[ExchangeID] [int] NOT NULL,
[IsSessionEnd] [bit] NOT NULL,
[OpeningDay] [int] NOT NULL,
[ClosingDay] [int] NOT NULL,
CONSTRAINT [PK_dbo.exchangesessions] PRIMARY KEY ([ID])
)

And then here are the queries done by the automatic migration:

ALTER TABLE [dbo].[Instruments] ALTER COLUMN [Expiration] [datetime]

ALTER TABLE [dbo].[exchangesessions] ALTER COLUMN [OpeningTime] [time](3) NOT NULL

ALTER TABLE [dbo].[exchangesessions] ALTER COLUMN [ClosingTime] [time](3) NOT NULL

As you can see, AutomaticMigrationsEnabled = false; I don't want to use automatic migration. — ASOF
So, there is a difference in the signature between the code and the database. How do you update the database? — Peter Smith
I update it by calling Database.Initialize() at startup. It seems that there is a difference, but: as I said, running Add-Miration doesn't pick up on any changes. Additionally, when I turn on automatic migration, no changes are actually made to the database. It makes a bunch of ALTER queries to change some Time columns, but it's not actually changing them at all. I have updated the question with the queries that are sent. — ASOF

Peter Smith Peter Smith · Accepted Answer · 2014-01-20T22:20:55

Some thoughts: Date-precision - .NET/C# has DateTime, SQL server has DateTime and DateTime2. Add the convention

public class DateTime2Convention : Convention
{
    public DateTime2Convention()
    {
        this.Properties<DateTime>()
            .Configure(c => c.HasColumnType("datetime2"));
    }
}

then add

modelBuilder.Conventions.Add(new DateTime2Convention());

to

protected override void OnModelCreating(DbModelBuilder modelBuilder)

Create a similar one for Time and that should address that problem.

The other thing that is required is to generate the SQL script required. You can need to find the signature (MigrationID) of the last database update from the table __MigrationHistory and use that to either generate a script using

Update-Database -Script -SourceMigration:"201312141123260_MbrraceMigrations2" -TargetMigration:"201312191701134_MbrraceMigrations5"

or omit -Script to use automatic migrations.

Sorry it's a bit of a patchy answer but I hope that will get you going.

Entity Framework Code-First Migration Fails with SQL Server

1 Answers