EF 5 + SQL Server CE 4: How to specify custom location for database file?

Question

I am developing a client system that needs a small local database. I want to avoid installation of SQL Server Express and have decided to go with SQL Server 4.

I use Entity Framework 5 for data access and have created my custom context. Everything works fine in development where I can use app.config to either set specific file location or dynamic Data Source=|DataDirectory|\MyDatabase.sdf.

But on deploy I want the database to be located in the users documents folder:

\My Documents\ApplicationName\MyDatabase.sdf

How can I do that?

All I need is actually to be able to set custom connection string in code!

This is what I tried so far:

private MyApplicationDataContext(string connectionString)
    : base(connectionString)
{
}

public static MyApplicationDataContext CreateInstance()
{
    var directory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
    var path = Path.Combine(directory, @"ApplicationName\MyDatabase.sdf");

    //var connectionString = string.Format("provider=System.Data.SqlServerCe.4.0;provider connection string=\"Data Source={0}\"", path);
    var connectionString = string.Format("Data Source={0}", path);

    return new MyApplicationDataContext(connectionString);
}

As you can see I tried two kinds of connection strings but both caused exceptions.

Keyword not supported: 'provider'.

and

The provider did not return a ProviderManifestToken string.

Answer 1

Ah, I finally got it right!

I include the adjusted code if someone else has the same problem. The trick was to set the connection string on the Database.DefaultConnectionFactory

    private MyApplicationDataContext()
    { }

    public static MyApplicationDataContext CreateInstance()
    {
        var directory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        var path = Path.Combine(directory, @"ApplicationName\MyDatabase.sdf");

        // Set connection string
        var connectionString = string.Format("Data Source={0}", path);
        Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0", "", connectionString);

        return new MyApplicationDataContext();
    }

Answer 2

In EF 6 this can be done with DbConfiguration:

App.config:

<entityFramework codeConfigurationType="MyDbConfiguration, MyAssembly">
</entityFramework>

And inside your assembly create a class like:

public class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        SetProviderServices(SqlCeProviderServices.ProviderInvariantName, SqlCeProviderServices.Instance);
        var directory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        var path = Path.Combine(directory, @"ApplicationName\MyDatabase.sdf");          
        var connectionString = string.Format(@"Data Source={0}",path);
        SetDefaultConnectionFactory(new SqlCeConnectionFactory(SqlCeProviderServices.ProviderInvariantName, "", connectionString));
    }
}