Specifying the Directory to Create SQL CE Databases When Using Entity Framework

In the last few posts, I've been describing how to create instances of SQLCE in order to perform automated Integration Testing using NUnit and accessing the dB using Entity Framework. I covered creating the dB using both Entity Framework and the SQL CE classes. In particular, I wanted control over the directory the dB was created in but I didn't want to tie to a specific location rather let it use the current working directory.

Using the Entity Framework's DbContext constructor that takes the name of a connection string or database name, it's suddenly very easy to end up NOT creating the dB you expected where you expected it to be. This post shows how to avoid these. Generally speaking, the use of the DbContext constructor that takes a Connection String should be avoided unless the name of a connection string from the .config file is being specified.

Example 1 - Using the SqlCeEngine Class

1:  const string DB_NAME = "test1.sdf";  
2:  const string DB_PATH = @".\" + DB_NAME; // Use ".\" for CWD or a specific path  
3:  const string CONNECTION_STRING = "data source=" + DB_PATH;  
4:    
5:  using (var eng = new SqlCeEngine(CONNECTION_STRING))  
6:  {  
7:    eng.CreateDatabase();  
8:  }  
9:    
10:  using (var conn = new SqlCeConnection(CONNECTION_STRING))  
11:  {  
12:    conn.Open(); // do stuff with db...  
13:  }  
14:

The important thing to note is that the constructor for SqlCeEngine that takes an argument requires a Connection String, i.e., a string containing the "data source=...". Just specifying the dB path is not sufficient. To specify a specific directory, include the absolute or relative path. To specify the current working directory, e.g. bin\debug, then just use ".\".

Example 2 - Using DbContext (doesn't work)

1:  using (var ctx = new DbContext("test2.sdf"))  
2:  {  
3:    ctx.Database.Create();  
4:  }

This code appears to work but doesn't create an instance of an SQL CE dB as desired. Instead, it creates a localDB instance in the user's home directory. In my case: C:\Users\Pete\._test.sdf.mdf (& corresponding log file). This is not really surprising as Entity Framework had no way of knowing that a SQL CE dB should be created.

Example 3 - Using DbContext (does work)

1:  Database.DefaultConnectionFactory =  
2:    new SqlCeConnectionFactory(  
3:      "System.Data.SqlServerCe.4.0",  
4:      @".\", "");  
5:    
6:  using (var ctx = new DbContext("test2.sdf"))  
7:  {  
8:    ctx.Database.Create();  
9:    // do stuff with ctx...  
10:  }

The difference between the last and this example is changing the default type of dB that EF should create. As shown, this is done by installing a different factory.

The 3^rd parameter to SqlCeConnectionFactory is the directory that the dB should be created in. Just like the first example specifying ".\" means the current working directory and specifying an absolute path to a directory will lead to them being created there.

NOTE: As per the post Integration Testing with NUnit and Entity Framework, be aware that creating a dB using the Entity Framework results in the additional table '_MigrationHistory' being created which EF uses to keep the model and dB synchronized.

NOTE 1: Whereas SqlCeEngine is a SQL CE class from the System.Data.SqlServerCe assembly, SqlCeConnectionFactory appears to be part of the System.Data.Entity assembly which is part of the Entity Framework.

In the above example, the string passed to DbContext can be a name (of a connection string from the .config file) or a connection string. In this case, passing the name of the db, i.e., test2.sdf is equivalent to passing "data source=test2.sdf", well more or less. If the '.sdf' suffix is omitted with "data source", then the resultant dB is called test2 but if just test2 is passed, then the resulting dB will be called test2.sdf.

Example 4 - Using DbContext and the .config File

1:  using (var ctx = new DbContext("test5"))  
2:  {  
3:    ctx.Database.Create();  
4:  }

App or Web .config:

1:  <connectionStrings>  
2:    <add name="test5"  
3:      providerName="System.Data.SqlServerCe.4.0"  
4:      connectionString="Data Source=test5.sdf"/>  
5:  </connectionStrings>

This time, no factory is specified but the argument to DbContext is the name of a Connection String in the .config file. As can be seen, this contains similar information to that in the factory method enabling EF to create a dB of the correct type.

To use these, the instances of these databases rather than calling the create method on the context, just use the context directly or more likely in the case of EF, a derived context which brings us to one last example.

Example 5 - Using a Derived Context and .config File

1:  public class TestCtx : DbContext  
2:  {      
3:  }  
4:  using (var ctx = new TestCtx())  
5:  {  
6:    ctx.Database.Create();  
7:  }

App or Web .config:

1:  <connectionStrings>  
2:    <add name="TestCtx"  
3:      providerName="System.Data.SqlServerCe.4.0"  
4:      connectionString="Data Source=test6.sdf"/>  
5:  </connectionStrings>

If a derived context is created which will almost certainly be the case, then if an instance of this is created and a dB created, then EF will look for a Connection String in the .config file that has the same name as the context and take the information from there.