How to enable EF 4.3 Migrations to work with Sql Server Compact

Using EF 4.3 Code First Migrations with Sql Server Compact 4.0

To get EF 4.3 Code First working with a Sql Server Compact 4.0 database is very easy.

First of course you need to

PM> Install-Package EntityFramework

and

PM> Install-Package SqlServerCompact

Then you can create your Model and DbContext classes (see this walkthrough)

Now, before actually creating an instance of your DbContext class, you should set

Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");

This will allow your application to run against Sql Server Compact. In a console application a good place for this statement is the start of your Program.Main method. For web applications either use the EntityFramework.SqlServerCompact  NuGet package or add the line to the Global.asax.cs Application_Start method.

Now you should be able to run your application (providing your Model and queries are compatible with Sql Server Compact)

Note: using a TimeStamp field is not compatible with Sql Server Compact 4.0

So you also want to use EF Migrations. You can follow the migrations walkthough but before running the application or running 

PM> Enable-Migrations

We need to allow EF to know that we are using a Sql Server Compact database, so you need to provide that information in the new EF 4.3 Configuration File Settings.

Just add this to your configuration file:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="System.Data.SqlServerCe.4.0" />
    </parameters>
  </defaultConnectionFactory>
</entityFramework>

At this point you no longer need to set the Database.DefaultConnectionFactory in your code. However, you can keep it in your code to allow your ‘Release’ config file to be cleaner.

Next, if you want your application to upgrade the database at-run-time, which I assume will be often the case when working with a Sql Server Compact database, you can add the MigrateDatabaseToLatestVersion Initializer.  You can also configure this in the config file, but it is a bit cumbersome because of the generic parameters. So create a new Initializer class like:

    internal sealed class Initializer
        : MigrateDatabaseToLatestVersion<BlogContext, Migrations.Configuration>
    {
        // Wrapper to allow easier app.config configuration
    }

In which case you can update your config file to:

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="System.Data.SqlServerCe.4.0" />
      </parameters>
    </defaultConnectionFactory>
    <contexts>
      <context type="EF43SqlCompactMigrations.BlogContext, EF43SqlCompactMigrations">
        <databaseInitializer type="EF43SqlCompactMigrations.Initializer, EF43SqlCompactMigrations" />
      </context>
    </contexts>
  </entityFramework>

Again, alternatively you can set this in code after the Database.DefaultConnectionFactory code.

Database.SetInitializer<BlogContext>(new Initializer());

Now all you need to do when changing your database schema is to

PM> Add-Migration 'someusefullname'

And your existing databases will be updated when your application attempts to access them.

development
Posted by: Rudi Larno
Last revised: 17 Feb, 2012 01:52 PM

Comments

No comments yet. Be the first!

blog comments powered by Disqus