SQL Server CE Code First migrations problems

Question

I have a bunch of problems trying to enable (code-first) migrations for my SQL Server Compact 4.0 database for my desktop .NET app.

Enable-Migrations does work and the directory Migrations is created. After that when I try to run Add-Migration InitialMigration, I get:

Access to the database file is not allowed. [ 1914,File name = Logo.sdf,SeCreateFile ]

This is the first problem, but I solved it by running Visual Studio as Administrator... don't like that solution and also don't know if later in production it will work without the app being run in Admin mode. I let that problem aside for now...

My connection string:

<add name="LogoContext" 
     connectionString="Data Source=Logo.sdf" 
     providerName="System.Data.SqlServerCE.4.0"/>`

So after running Add-Migration InitialMigration in Administrator mode I get an empty migration... that's ok. Then I delete the migration and add a new class:

using System;
using System.Collections.Generic;

public class Blog
{
    public int ID { get; set; }
    public string Title { get; set; }
}

I add a reference to the context class:

public class LogoContext : DbContext
{
    public DbSet<Word> Words { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<Blog> Blogs { get; set; }
}

Then run Add-Migration InitialMigration again and get:

public partial class InitialMigration : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Blogs",
            c => new
                {
                    ID = c.Int(nullable: false, identity: true),
                    Content = c.String(maxLength: 4000),
                })
            .PrimaryKey(t => t.ID);
    }

    public override void Down()
    {
        DropTable("dbo.Blogs");
    }
}

After running Update-Database I see:

Applying code-based migrations: [201304211225255_InitialMigration].
Applying code-based migration: 201304211225255_InitialMigration.
Running Seed method.

Now the problem appears - in my Server Explorer I examine the database Logo.sdf and it does not include the table Blogs! I even try to run this code from my app:

var db = new LogoContext();
db.Posts.Add(new Blog { Title= "moo" });
db.SaveChanges();

to check if maybe my Server Explorer isn't showing the table.. but I get an exception:

The specified table does not exist. [ Blogs ]

So the migrations are obviously not being applied to my Logo.sdf file :(

If I remove the connection string from app.config, the connection to a local instance of SQL Server Express is assumed. And there it works flawlessly!! When I examine the database with SQL Server Management Studio, I see the new Blogs table and also a system table for metadata about migrations...

Another little piece of information:

When I try to run Update-Database again, I get "No pending code-based migrations." and that tells me that some data is being saved to Logo.sdf after all... at least some metadata about migrations, but still I can't see that table in Server Explorer.

I'm using VS 2012 and EF 5.0.

Please help me understand this... It looks to me that something is seriously wrong because it just works with SQL Server Express instance, but not with SQL Server CE 4.0. :((

Thank you! david

Answer 1

So the problem was that the solution created a separate .sdf file here:

"C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\Logo.sdf"

Which was unexpected and strange IMHO...

I ended up using this connection string:

<add name="LogoContext" connectionString="Data Source=|DataDirectory|\Logo.sdf" providerName="System.Data.SqlServerCE.4.0"/>

This references bin/Debug/Logo.sdf and it works during development and when running a .exe separately.

The only thing with this way is that my Logo.sdf project file (which was getting copied to Debug "if newer") is now completely ignored. All the migrations will be run on the Debug file.. That's probably good too....

Thanx Erik for the hint! david