Connection string for using SQL Server Compact with Entity Framework?

Question

I'm done trying to Google for this. I have installed SQL Server CE 4.0, and have EF 4.1, but I can't get a proper connection string. Nothing on connectionstrings.com applies to me.

I just want to create a SqlCeEngine object, but no matter what I try I get some exception. Most recently it's been

Unknown connection option in connection string

with either "metadata", "app", "provider", or "provider connection string" after it. I know EF requires metadata in the connection string. And I can't imagine how anything could do without "provider connection string".

So far I have this:

<add name="DBContext" 
     connectionString="provider connection string=&quot;Data Source=MyDbFile.sdf;Persist Security Info=False;&quot;" 
     providerName="System.Data.EntityClient" />

At one point I had it with metadata:

<add name="DBContext" 
     connectionString="metadata=res://*/Data.DBContext.csdl|res://*/Data.DBContext.ssdl|res://*/Data.DBContext.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=MyDbFile.sdf;Persist Security Info=False;&quot;" 
     providerName="System.Data.EntityClient" />

Does it need metadata or not? What goes in the "app" part of the connection string? What should the provider be, System.Data.SqlClient or some SQL Server CE version? (which I still can't find when I try to add references. My add references window still only contains System.Data.SqlServerCe version 3.5.1.0.) Or nothing?

And what should go in the providerName attribute? Is System.Data.EntityClient correct? It's like there are 10 different variables here and every combination gives me a new equally mysterious error, none of which turns up anything useful on Google. I'm at my wits' end. Is this even possible?

Answer 1

You could try this in your App.config file (EF5 Code first migrations and SQL Server CE 4.0):

<connectionStrings>
    <add name="DefaultConnection"
         providerName="System.Data.SqlServerCe.4.0"
         connectionString="Data Source=|DataDirectory|\Data\ProjectDb.sdf"/>
</connectionStrings>

And in you ProjectDb class:

class ProjectDb : DbContext
{
    public ProjectDb()
        : base("DefaultConnection")
    {

    }
}

It will work like a charm.

You can find more information here: http://blogs.msdn.com/b/adonet/archive/2011/01/27/using-dbcontext-in-ef-feature-ctp5-part-2-connections-and-models.aspx

Answer 2

I’d stuck with the same problem on Entity Framework 5.0 Code First approach with SQL Server Compact Edition 4.0

To solve it I pass instance of SqlCeConnection to DbContext's constructor:

public class Storage : DbContext
{
    public Storage()
        : base(new SqlCeConnection("Data Source=Database.sdf;Persist Security Info=False;"),
             contextOwnsConnection: true)
    { }

    //...
}

If you don’t want to inline connection string you could get it from App.config through ConfigurationManager And of course you should add reference to the right version of System.Data.SqlServerCe (look inside Extentions tab in the Add Reference dialog)