In the previous post, Using NUnit and Entity Framework DbContext to programmatically create SQL Server CE databases and specify the database directory, a basic way was shown to how to create a new dB (using Entity Framework's DbContext) programmatically. This was used to generate a new dB for a test hosted by NUnit.
The subsequent post Generating a SQL Server CE database schema from a SQL Server database using Entity Framework showed how to generate a SQL CE dB schema from an existing SQL Server database.
This post ties the previous ones together. As mentioned in the first post, the reason for this is an attempt at what amounts to Integration Testing using NUnit. I'm currently building a Repository and Unit Of Work abstraction on top of Entity Framework which will allow the isolation of the dB code (in fact, it will isolate and abstract away most forms of data storage). This means any business logic can be tested with a test-double that implements the Repository and UnitOfWork interfaces; which is straight forward Unit Testing. The Integration Testing is to verify that the Repository and Unit Of Work implementations work correctly.
The rest of the post isn't focused on these two patterns; though it may mention them. Instead, it documents my further experience of using NUnit to writes tests that interact with dB via Entity Framework. The premise for this is that a dB already exists.
As such, the approach to using Entity Framework is a hybrid of Database First and Code First in that the dB schema exists and needs be maintained outside of EF and also that EF should not generate model classes, i.e., allowing the use of Code First POCOs. This is possible as the POCOs can be defined, a connection made to dB and then the two are conflated via an EF DbContext. It then seems that EF creates the model on the fly (internally compiles it) and as long as the POCO types map to the dB types, then it all works as if by magic!The advantage of doing it this way is that the existing dB is SQL Express based but for the Integration Testing, a new dB can be created when needed, potentially one per test. In order to keep the test dBs isolated from the real dB, SQL Server Compact Edition (SQL Server CE V4) was used. Therefore, the requirement was for the EF code to be able to work with SQL Express and SQL CE with the primary definition of the schema taken from SQL Express. It's not possible to use exactly the same schema as SQL CE only has a subset of the data-types provides by SQL CE. However, the process described in the post Generating a SQL Server CE database schema from a SQL Server database using Entity Framework showed how to create semantically equivalent SQL.
From this point onwards, it's assumed that a SQL file to create the dB has been generated. Now create a new C# class library project and using the NUGet, add Entity Framework, NUnit and SQL CE 4.0. All my work has been with EF 4.3.1. Following this, drag the Model1.edmx.sqlce file from the project used to generate to new project. You may wish to rename it, e.g. to test.sqlce.
Creating the Database
The post Generating a SQL Server CE database schema from a SQL Server database using Entity Framework showed how to create a new CE dB per-test using the EF DbContext to do the hard work. A different approach is now taken as the problem with creating a dB using DbContext is that in addition to creating any specified tables and indices, etc., it also creates an additional table called '__MigrationHistory' which contains a description of the EF model used to create the dB. The description of the problem caused by this will be delayed until the "Why DbContext is no longer used to create the database" section. Suffice to say for the present using the new mechanism avoids the creation of this table.
The code below is the beginning of a test class. It is assumed that all the tests need a fresh copy of the dB hence the creation is performed in the Setup method. All this code does is create a SQL CE dB and then creates the schema.[TestFixture]
public class SimpleTests
{
const string DB_NAME = "test.sdf";
const string DB_PATH = @".\" + DB_NAME;
const string CONNECTION_STRING = "data source=" + DB_PATH;
[SetUp]
public void Setup()
{
DeleteDb();
using (var eng = new SqlCeEngine(CONNECTION_STRING))
eng.CreateDatabase();
using (var conn = new SqlCeConnection(CONNECTION_STRING))
{
conn.Open();
string sql=ReadSQLFromFile(@"C:\Users\Pete\work\Jub\EFTests\Test.sqlce");
string[] sqlCmds = sql.Split(new string[]
{ "GO" }, int.MaxValue, StringSplitOptions.RemoveEmptyEntries);
foreach (string sqlCmd in sqlCmds)
try
{
var cmd = conn.CreateCommand();
cmd.CommandText = sqlCmd;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.Error.WriteLine("{0}:{1}", e.Message, sqlCmd);
throw;
}
}
}
public void DeleteDb()
{
if (File.Exists(DB_PATH))
File.Delete(DB_PATH);
}
private string ReadSQLFromFile(string sqlFilePath)
{
using (TextReader r = new StreamReader(sqlFilePath))
{
return r.ReadToEnd();
}
}
}
The dB file (Test.sdf) will be created in the current working directory. As the test assembly is located in <project>\bin\debug which is where the NUnit test runner picks up the DLL from this directory, this is where it is created. If a specific directory is required, then the '.\' can be replaced with the required path.
The Setup method is marked with NUnit's SetUp attribute meaning it will be invoked on a per-test basis creating a new dB instance for each test. The DeleteDb method could be marked with [TearDown] attribute but at the moment, any previous dB is deleted before creating a new one. It would be fine to do both as a belt and braces approach. The reason I didn't make it the TearDown method is so that I could inspect the dB following a test if needed.
SQL CE does not support batch execution of SQL scripts which is where it gets interesting as the SQL generated previously is in batch form. The code reads the entire file into a string and determines each individual statement by splitting string on the 'GO' command that separates each SQL command.
To help understand the SQL, following is the diagram of the dB I'm working with. All fields are strings except for the Ids which are numeric.
Each of these commands is then executed. The previously generated SQL (the SQL for the dB I'm working with is below) will not work completely out of the box. The ALTER and DROP statements at the beginning don't apply as the schema is being applied to an empty dB, these should be removed. Interestingly, the schema generation step for my dB seems to miss out a 'GO' between the penultimate and ultimate statement. I had to add one by hand. Finally, the comments at the end prove a problem as there is no terminating 'GO'. Removing these fixes the problem. In the code above, the exception handler re-throws the exception after writing out the details. For everything to proceed, the SQL needs modifying to execute perfectly. If the re-throw is removed, then the code will tolerate individual command failures which in this context really just amount to warnings.
NOTE: Text highlighted in red has been removed and text in blue added.
ALTER TABLE [RepComments] DROP CONSTRAINT [FK_RepComments_Reps];
GO
DROP TABLE [RepComments];
GO
DROP TABLE [Reps];
GO
DROP TABLE [Roads];
GO
CREATE TABLE [RepComments] (
[CommentId] int IDENTITY(1,1) NOT NULL,
[RepId] int NOT NULL,
[Comment] ntext NOT NULL
);
GO
CREATE TABLE [Reps] (
[RepId] int IDENTITY(1,1) NOT NULL,
[RepName] nvarchar(50) NOT NULL,
[RoadName] nvarchar(256) NOT NULL,
[HouseNumberOrName] nvarchar(50) NOT NULL,
[ContactTelNumber] nvarchar(20) NOT NULL,
[Email] nvarchar(50) NULL
);
GO
CREATE TABLE [Roads] (
[Name] nvarchar(256) NOT NULL
);
GO
ALTER TABLE [RepComments]
ADD CONSTRAINT [PK_RepComments]
PRIMARY KEY ([CommentId] );
GO
ALTER TABLE [Reps]
ADD CONSTRAINT [PK_Reps]
PRIMARY KEY ([RepId] );
GO
ALTER TABLE [Roads]
ADD CONSTRAINT [PK_Roads]
PRIMARY KEY ([Name] );
GO
ALTER TABLE [RepComments]
ADD CONSTRAINT [FK_RepComments_Reps]
FOREIGN KEY ([RepId])
REFERENCES [Reps]
([RepId])
ON DELETE NO ACTION ON UPDATE NO ACTION;
GO
CREATE INDEX [IX_FK_RepComments_Reps]
ON [RepComments]
([RepId]);
GO
Getting the SQL into a state where it will run flawlessly is a little bit of a hassle but given the number of times it will be used subsequently, it's job is a big job, well for a small dB anyway. To verify that your dB has been created as needed, a quick and easy way to test is to comment out the call to DeleteDb() and after a test has run, open to the dB using Server Explorer within VS, i.e.,
Using the dB in a Test
Now that a fresh dB will be created for each test, it's time to look at simple test:
[Test]
public void TestOne()
{
using (var conn = new SqlCeConnection(CONNECTION_STRING))
using (var ctx = new TestCtx(conn))
{
ctx.Roads.Add(new Road() { Name = "Test" });
ctx.SaveChanges();
Assert.That(1, Is.EqualTo(ctx.Roads.Count()));
}
}
Road in this case is defined as:
class Road
{
[Key]
public string Name { get; set; }
}
and TestCtx as:
class TestCtx : DbContext
{
public TestCtx(DbConnection conn) : base(conn, false)
{
}
public DbSet<Road> Roads { get; set; }
}
The first thing to note is that EF is not used to form the connection to the dB, instead one is made using the SqlCe specific classes. Attempting to get EF to connect to a specific dB instance when not referring to a named connection strings in the .config file is a bit of an art (I may write another entry about this). However, EF is quite happy to work with an existing connection. This makes for a good separation of responsibilities in the code where EF manages the interactions with the dB but the control of the connection is elsewhere.
NOTE: It is likely that each test will require a connection and a context, hence rather it might make more sense to move the creation of the SqlCeConnection and the context (TestCtx in this case) to a SetUp method and as these resources need disposing of adding a TearDown method to do that. TestCtx could also be modified to pass true to the DbContext constructor to give ownership of the connection to the context so that it will dispose of it then context is disposed off.
I would have preferred to avoid having to defined a specific derived context and instead use DbContext directory, e.g.
[Test]
public void TesTwo()
{
using (var conn = new SqlCeConnection(CONNECTION_STRING))
using (var ctx = new DbContext(conn, false))
{
ctx.Set<Road>().Add(new Road() { Name = "Test" });
ctx.SaveChanges();
Assert.That(1, Is.EqualTo(ctx.Set<Road>().Count()));
}
}
However, when SaveChanges() is called, the following exception is thrown:
System.InvalidOperationException: The entity type Road is not part of the model for the current context. This is because EF knows nothing about the Road type. When a derived context is created for the first time, I think EF performs reflection on any properties that expose DbSet. These are the types that form the Model. Another option is to create the model, optionally compile it and then pass it to an instance of DbContext. This way involves a lot less code.
That's it. The final section is just a footnote about the move away from using EF to create the dB.
Why DbContext is No Longer Used to Create the Database
As mentioned, creating the dB using...
using (var ctx = new DbContext("bar.sdf"))
{
ctx.Database.Create();
}
...causes the '__MigrationHistory' table to be created. Assuming this method was used, later on when TestCtx was used to open the dB and perform an operation, the following exception would be thrown:
System.InvalidOperationException : The model backing the 'DbContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).
This is because the context used to create the model was a raw DbContext (as per the previous post) whereas the dB was accessed via the TestCtx. If the context used to create the dB is also changed to TestCtx, then this problem goes away. However, given the original dB is not intended to be created nor be maintained (code migrations) by EF, then using the non-context/EF approach to dB completely removes EF from the picture.
