Code First Migrations and initialization error

c# ef-code-first sql-server-ce-4 entity-framework-migrations
Martin1921 picture Martin1921 · Jul 26, 2012 · Viewed 14.4k times · Source

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"

Answer

BenSwayne picture BenSwayne · Aug 10, 2012

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!

Related questions

The entity type <type> is not part of the model for the current context

I am getting into the Entity Framework, but I am unsure if I am missing a critical point in the code-first approach. I am using a generic repository pattern based on the code from https://genericunitofworkandrepositories.codeplex.com/ and have …

Read More
Ignoring a class property in Entity Framework 4.1 Code First

My understanding is that the [NotMapped] attribute is not available until EF 5 which is currently in CTP so we cannot use it in production. How can I mark properties in EF 4.1 to be ignored? UPDATE: I noticed something else strange. …

Read More
How to fix the datetime2 out-of-range conversion error using DbContext and SetInitializer?

I'm using the DbContext and Code First APIs introduced with Entity Framework 4.1. The data model uses basic data types such as string and DateTime. The only data annotation I'm using in some cases is [Required], but that's not on any …

Read More