Code First Migrations and initialization error

Question

I'm unsure about how to use the code first migration feature. In my understanding it should create my database if it's not existing already, and update it to the latest schema according to migration files. But I'm struggling with it, because I always get a lot of errors and I'm unsure overall how to use this properly..

internal class Program
{
    private static void Main()
    {
        EntityFrameworkProfiler.Initialize();

        Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Migrations.Configuration>());

        using (var context = new MyContext())
        {
            var exists = context.Database.Exists();
            if (!exists)
            {
                context.Database.Create();
            }

            var element = context.Dummies.FirstOrDefault();
        }
    }
}

public class MyContext : DbContext
{
    public MyContext()
        : base(string.Format(@"DataSource=""{0}""", @"C:\Users\user\Desktop\MyContext.sdf"))
    {
    }

    public DbSet<Dummy> Dummies { get; set; }
}

internal sealed class Configuration : DbMigrationsConfiguration<MyContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
    }

    protected override void Seed(CodeFirstTest.MyContext context)
    {
    }
}

Using the Entity Framework Profiler I check what statements are executed. When I run the program with no database existing I get the following output:

-- statement #1 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #2 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteDbDataReader(CommandBehavior behavior) at HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledCommand.ExecuteDbDataReader(CommandBehavior behavior)

-- statement #3 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #4 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteDbDataReader(CommandBehavior behavior) at HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledCommand.ExecuteDbDataReader(CommandBehavior behavior)

-- statement #5 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #6 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteDbDataReader(CommandBehavior behavior) at HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledCommand.ExecuteDbDataReader(CommandBehavior behavior)

-- statement #7 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #8 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ] at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteDbDataReader(CommandBehavior behavior) at HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledCommand.ExecuteDbDataReader(CommandBehavior behavior)

-- statement #9 begin transaction with isolation level: Serializable

-- statement #10 CREATE TABLE [Dummies] ( [Name] nvarchar NOT NULL, CONSTRAINT [PK_Dummies] PRIMARY KEY ([Name]) )

-- statement #11 CREATE TABLE [MigrationHistory] ( [MigrationId] nvarchar NOT NULL, [CreatedOn] [datetime] NOT NULL, [Model] [image] NOT NULL, [ProductVersion] nvarchar NOT NULL, CONSTRAINT [PK_MigrationHistory] PRIMARY KEY ([MigrationId]) )

-- statement #12 INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201207261524579_InitialCreate', '2012-07-26T15:24:58.523', 0x1F8B080 , '4.3.1')

-- statement #13 commit transaction

-- statement #14 SELECT TOP (1) [c].[Name] AS [Name] FROM [Dummies] AS [c]

As you can see it is trying to access the database four times before it actually creates the database. This does not seem right. When I start the application with an existing database it'll query the database 7 times before any of my actual queries will be executed. Note that this happens with context.Database.Create(), not with .Exists().

Also the seed method of my configuration is never called, but the constructor is.

This all just seems very wrong and confusing. I hope someone can enlighten me why the errors happen so often in the beginning, and why my seed method is not called at all.

I'm using the latest stable versions of SqlServer compact and Entity Framework.

package id="EntityFramework" version="4.3.1" targetFramework="net40"

package id="Microsoft.SqlServer.Compact" version="4.0.8854.2" targetFramework="net40"

Solution 1

It seems there are a lot of ways to configure entityframework and everyone has there own take on what is best. All I can offer is my take based on what we've standardized at my work. A lot of this is developer preference. My preference happens to be controlling as much as possible so I always understand exactly what is happening and when.

Automatic Migrations

First off, while Automatic Migrations may be convenient but they cause a lot of trouble particularly as a project grows and/or data becomes more complex. In my opinion any commercial/production system should have more control than this. We always turn off automatic migrations for all of our major projects by setting AutomaticMigrationsEnabled = false;. We run our migrations explicitly when we want it done (on dev this is in the package manager console in visual studio by typing Update-Database and in production we have written our own little migration utility that just calls the migrate to latest code explicitly - but none are automatic).

@Terric's answer scares me with both automatic migrations AND data loss being permitted! I don't want to be the guy who deploys a solution and wipes out some important data because of a badly executed column alteration that resulted in data loss. As a side note when we run out migration explicitly in dev I often use the -v switch for verbose ouptut (Update-Database -v). This lets you see the SQL being executed and any failures/warnings if appropriate.

It has also been our experience that changing these settings after you are several migrations into development doesn't go well. I'm not sure where this is being tracked, but starting a project fresh with automatic migrations disabled ensures nothing unexpected is going to happen.

Personally, I'd remove the Initializer you have MigrateDatabaseToLatestVersion and run the migrator myself exactly when I want to (either via the package manager console or via my own explicit code somewhere).

Creating a database if it doesn't exist

This behavior is provided by a DatabaseInitializer (not really EntityFramework itself). The CreateDatabaseIfNotExists initializer is built into EntityFramework and a default in some versions. However, again I'm not one for all the inferred app behavior stuff. In my opinion I'd like a little more control.

This guy has an example of a custom database initializer inheriting from the built in CreateDatabaseIfNotExists. But you could always just create your own and implement whatever exact logic you want to see (including the creation of your database). Again this just avoids unexpected behavior. My personal preference as a developer is to control this stuff closely unless I'm just goofing around with a mockup or test project.

Super simple custom DatabaseInitializer with no unexpected behavior:

namespace MyProject.Data.DatabaseInitializers
{
    public class MyCustomDbInit<TContext> : IDatabaseInitializer<TContext>
        where TContext : DbContext
    {
        public void InitializeDatabase(TContext context)
        {
            // Create our database if it doesn't already exist.
            context.Database.CreateIfNotExists()

            // Do you want to migrate to latest in your initializer? Add code here!

            // Do you want to seed data in your initializer? Add code here!
        }
    }
}

The Results

If you use a code first approach, disable automatic migrations and use a custom DatabaseInitializer like the above, you will have very good control over what is happening and when.

We use these strategies at work and have zero issues (although it did take some trouble to settle on these strategies). Hopefully you will find similar success!

Solution 2

I was able to replicate your problem using SQL CE, as well as using EF code first using the code above.

The strange thing is that when I used your code as is, the first time, it worked perfectly. In order to have your issue arise, I had to actually delete the _MigrationHistory table in my .sdf file.

By deleting the .sdf file (I realize this might not be an option in your case, but I'll address that farther down) The next time it ran, it created the migration table - but it still didn't work quite properly. If you notice, at step 12 it's finally creates the table.

-- statement #1 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [Dummies] AS [Extent1]) AS [GroupBy1]

-- statement #2 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #3 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ]

-- statement #4 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #5 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ]

-- statement #6 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #7 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ]

-- statement #8 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #9 WARN: System.Data.SqlServerCe.SqlCeException (0x80004005): The specified table does not exist. [ __MigrationHistory ]

-- statement #10 begin transaction with isolation level: Serializable

-- statement #11 CREATE TABLE [Dummies] ( [DummyId] [int] NOT NULL IDENTITY, [test] nvarchar, [addThis] nvarchar, CONSTRAINT [PK_Dummies] PRIMARY KEY ([DummyId]) )

-- statement #12 CREATE TABLE [__MigrationHistory] ( [MigrationId] nvarchar NOT NULL, [CreatedOn] [datetime] NOT NULL, [Model] [image] NOT NULL, [ProductVersion] nvarchar NOT NULL, CONSTRAINT [PK___MigrationHistory] PRIMARY KEY ([MigrationId]) )

-- statement #13 INSERT INTO [__MigrationHistory] ([MigrationId], [CreatedOn], [Model], [ProductVersion]) VALUES ('201208101940587_InitialCreate', '2012-08-10T19:40:59.055', 0x1F8B0800000000000400CD57DD8ED33C10BDFF24DE21F23D75591082550A5ADA5D5441771159B89FC6D3D6C27642ECACDA67FB2E78245E8171FE9A26FDD95D40E2AE998CCF1C1F9FF1A43FFFFF11BE5D6B15DC6166656246ECD960C802347122A4598E58EE164F5FB1B76F9EFC175E0ABD0EBED679673E8F561A3B622BE7D273CE6DBC420D76A0659C253659B8419C680E22E167C3E12BFE6CC8912018610541F839374E6A2C1EE8719C98185397839A250295ADE2F4262A50836BD068538871C4DE8195F198D2AE64661D0B2E94046211A15A3C90D2F0B5A7C49A6254EE9268B9CDED26C5A2E4884D72AD37ED144AFA809B9D00853E65498A99DB7CC6457BE154B080EF2EE6DDD5CDDAEE42CF62C4A6C63D3F63C175AE14CC150516A02CB2207D791EB924C3F768300387E2133887199DCD5460B18B4A8DF3F4E5FD0479CD87675E100EC6240E1C1D748F7D87AB437F0225D1C865E419165CC9358A8F68966ED5909DC1BA8EBC180EC93A5F8C248F79842CC7F6EECAE7E3554188DB95B47FBB70C8B76EE87B842CEB4092F615A95911C1B5EB98A5CC8ED0B58E572291DF8297E61D9456DB4BA129B66D0C5E7646DD41FC400B8533485392A7D552552488CA7E1A3F8D1EDE37BAC4E0B1DDD33E0DDBA612191596D8794BA5EB2E9E808339F8031B0BDD4B3B296D5D6757E16E8F6E05AFF3FDEF72CDEEA532D8D3F56DE9AE68379A9AACD8183634F6DE16D5CA280605D9A1561F272AD7E6E8C5710CA76CC3364819B93F42D3526D9026D8C70979478AAED8BCA776E71AEC1EDE31D777539AEA8DFB3B2E0F2BC79D9E263D0B96292C2081EEA4F0F68B36D6A11EF88441F45D4598D1B81CE3E0859F8175DA0C8C5C90E8B7C937A43BB878F7D8C9D45CC4D60AF58F8F2769C8672707516F7ADD7FAC983BC8E215647BEEF2DF9A1A8FC47DD850E85F70A787C3E6F06828BD3962629ED0364A8ACD3C79E4DCE8774AC8DB5F67E104AD5C6E21FCB79AC1D87F1D6C41EB9CA95924B5ECB4AB36A33AA5732A337420489C8BCCC905C48E5EC7686D31D0BF82CA29E552CF514CCD4DEED2DC5D588B7AAE76E664C88FD72F86E32EE7F026F54FF64F6C81684ADA02DE9877B954A2E17DD5B7FA2108EF93AA7F88157DD010DC72D3205D27E69E40957C134CD1F8EEBB459D2A02B33726823B3CCCEDB486BB8A851309CB0C745BC132523189802AB74A5081F68A6D3DFFE782FB7F176F7E018E4F04B08F0C0000 , '4.3.1')

-- statement #14 commit transaction

-- statement #15 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [Dummies] AS [Extent1]) AS [GroupBy1]

Which fixes the problem by creating the table in the right place:

Once I did this, the next time the code ran, everything ran perfectly again.

-- statement #1 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #2 SELECT TOP (1) [c].[ProductVersion] AS [ProductVersion] FROM [__MigrationHistory] AS [c]

-- statement #3 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #4 SELECT [Extent1].[MigrationId] AS [MigrationId] FROM [__MigrationHistory] AS [Extent1]

-- statement #5 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #6 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #7 SELECT TOP (1) [Project1].[C1] AS [C1], [Project1].[MigrationId] AS [MigrationId], [Project1].[Model] AS [Model] FROM (SELECT [Extent1].[MigrationId] AS [MigrationId], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[Model] AS [Model], 1 AS [C1] FROM [__MigrationHistory] AS [Extent1]) AS [Project1] ORDER BY [Project1].[CreatedOn] DESC

-- statement #8 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [__MigrationHistory] AS [Extent1]) AS [GroupBy1]

-- statement #9 SELECT TOP (1) [Project1].[C1] AS [C1], [Project1].[MigrationId] AS [MigrationId], [Project1].[Model] AS [Model] FROM (SELECT [Extent1].[MigrationId] AS [MigrationId], [Extent1].[CreatedOn] AS [CreatedOn], [Extent1].[Model] AS [Model], 1 AS [C1] FROM [__MigrationHistory] AS [Extent1]) AS [Project1] ORDER BY [Project1].[CreatedOn] DESC

-- statement #10 begin transaction with isolation level: Unspecified

-- statement #11 insert [Dummies] ([test], [addThis]) values (null, null);

select [DummyId] from [Dummies] where [DummyId] = @@IDENTITY

-- statement #12 insert [Dummies] ([test], [addThis]) values (null, null);

select [DummyId] from [Dummies] where [DummyId] = @@IDENTITY

-- statement #13 insert [Dummies] ([test], [addThis]) values (null, null);

select [DummyId] from [Dummies] where [DummyId] = @@IDENTITY

-- statement #14 insert [Dummies] ([test], [addThis]) values (null, null);

select [DummyId] from [Dummies] where [DummyId] = @@IDENTITY

-- statement #15 commit transaction

-- statement #16 SELECT [GroupBy1].[A1] AS [C1] FROM (SELECT COUNT(1) AS [A1] FROM [Dummies] AS [Extent1]) AS [GroupBy1]

If dropping the entire SDF and letting it recreate is not an option, here's what I did to get it back.

Create a connectionstring in your appconfig (I realize you're probably wanting dynamic connection strings that's why it's in your Code, but this should be a one time thing). My connection string looked like this:

   <connectionStrings>
        <add connectionString="Data Source=MyContext.sdf;Persist Security Info=False;" name="MyContext" providerName="System.Data.SqlServerCe.4.0"/>
    </connectionStrings>

Change the constructor of your Context so that it will use the connection string:

public MyContext()
    //: base(string.Format(@"DataSource=""{0}""", "MyContext.sdf"))
    : base("MyContext")

All of this is necessary so you can run some commands in Package Manager Console, so that it will re-create the table. Open up package manager console, and run this command:

add-migration initial -ignorechanges

Next, run the program - it will throw some warnings, but then it will create the table for you, and populate it. After that, you can change your constructor back and I had no more issues.

Note: once it started working the Seed Function started working as well

Solution 3

I played around with the code you provided and in this case (with SQL Server instead of CE) and have arrived at the following. I've removed the Database.Create code and allowed EF's automatic migrations to do it's thing. This runs through and calls the Seed method correctly now.

internal class Program
{
    private static void Main()
    {
        EntityFrameworkProfiler.Initialize();

        Database.DefaultConnectionFactory = new SqlConnectionFactory("System.Data.SqlServer");
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, MyContextConfiguration>());

        using (var context = new MyContext())
        {
            var element = context.Dummies.FirstOrDefault();
        }
    }
}

internal class Dummy
{
    public String Id { get; set; }
}

internal sealed class MyContext : DbContext
{
    public MyContext() : base(@"Data Source=localhost;Initial Catalog=Dummies;User Id=<USER_ID>;Password=<PASSWORD>;MultipleActiveResultSets=False;")
    {
    }

    public DbSet<Dummy> Dummies { get; set; }
}

internal sealed class MyContextConfiguration : DbMigrationsConfiguration<MyContext>
{
    public MyContextConfiguration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
    }

    protected override void Seed(MyContext context)
    {
        context.Dummies.AddOrUpdate(new Dummy() { Id = "First" });
    }
}

If you look in EF profiler, you'll see there are more queries run against the DB now (and even a check for the old EdmMetaData table... which is very odd, as it should drop that table if it's encountered now in favour of the __MigrationHistory table). I don't know why this is happening, I guess it's either a configuration issue our side (of which I don't yet know how to fix) or it's a bug in the migrations code.

So, I think with EF migrations we're either left to code based migrations (see my blog post here) or automatic migrations (as this code snippet demonstrates). I guess as time goes on I'll get a better understanding of why EF (or the way I migrate) has this strange behaviour - or EF itself will get better as it evolves.

Comments

[Sergei Rogovtcev]

Try to move both Database static calls out of constructor and before first call to DbContext.

[Martin1921]

@SergRogovtsev This unfortunately changed nothing.

[Sergei Rogovtcev]

Would you please update your code in question accordingly?

[Paul Aldred-Bann]

@Martin1921 Well, you're certainly right. I've tried this snippet myself and I can see that if the database doesn't exist it checks 4 times for the presence of __MigrationHistory table (I tried setting AutomaticMigrationsEnabled = false in case it was duplicating for your manual migration and EF's auto migration but still the exact same thing). My seed method runs when I run my code a 2nd time (after the initial DB create) but not the first time.

[Martin1921]

Removing the Database.Create() methodcall changes not a single thing. Apparently it must be related to the CE server edition, and unfortunately SQL server is no option in this case.

[Paul Aldred-Bann]

@Martin1921 really? So setting automatic migrations to true and removing the whole Database.Exists and Database.Create check changes nothing? Very strange... I'll try getting hold of a copy of SQL Server CE tonight and seeing what happens.

[Kumar Vaibhav]

I think you have given the most detailed answer! Very informative comments. Thanks.