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;
3: const string CONNECTION_STRING = "data source=" + DB_PATH;
5: using (var eng = new SqlCeEngine(CONNECTION_STRING))
10: using (var conn = new SqlCeConnection(CONNECTION_STRING))
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"))
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(
4: @".\", "");
6: using (var ctx = new DbContext("test2.sdf"))
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 3rd 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
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"))
App or Web .config:
2: <add name="test5"
4: connectionString="Data Source=test5.sdf"/>
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
4: using (var ctx = new TestCtx())
App or Web .config:
2: <add name="TestCtx"
4: connectionString="Data Source=test6.sdf"/>
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.