Set the SQL Server CE connection string for Entity Framework at runtime

Typically when you create your EF data model wizard will politely add all necessary settings in you app or web.config. That's all fine and dandy, but what if you have SQL Compact Database and you have multiple copies or database names could change and run-time. There is tone of other reasons why you might not want to store your connection strings in plain text config files. Basically you must pass connection string to your Entity Model at run-time.

First we need to extend our generated DbContext class with additional constructor that accepts connection string and pass it on to base class. At the moment of writing, this applies to EF 6.x and EF 5.x and I am using Database first approach, not sure if it works for Code First. Generated DbContext partial class can be found in solution tree if you expand .edmx file it is the one with EdmxFileName.Context.cs name.      

public partial class DBEntities : DbContext
    {
        public DBEntities()
            : base("name=DBEntities")
        {
        }

        public DBEntities(string connectionString)
            : base(connectionString) {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }}}

 public static string GetSqlCeConnectionString(string fileName, string password) {
            var connectionStringBuilder = new SqlCeConnectionStringBuilder();
            connectionStringBuilder.DataSource = fileName;
            connectionStringBuilder.Password = password;
            var entityConnectionStringBuilder = new EntityConnectionStringBuilder();
            entityConnectionStringBuilder.Metadata = "res://*/YourEdmxFileName.csdl|res://*/YourEdmxFileName.ssdl|res://*/YourEdmxFileName.msl";
            entityConnectionStringBuilder.Provider = "System.Data.SqlServerCe.4.0";
            entityConnectionStringBuilder.ProviderConnectionString = connectionStringBuilder.ToString();

            return entityConnectionStringBuilder.ConnectionString;
        }

Next, just pass that connection string to DBContext constructor:

using (var db = new DBEntities(connectionString)) {
....
}

If something goes wrong when connecting to database or accessing compiled resources this blog post can be very useful.