Summary
I’ve published a few blog posts on the usage of SQLLocalDB with NHibernate. Now I’m going to demonstrate how easy it is to use with EF. In fact, SQLLocalDB can be used with ADO queries and LINQ-To-SQL. If you’re dealing with legacy code and your methods use a lot of database access operations, it would be beneficial to get your code in a test harness before converting to your target ORM.
Modifying the EF Context
The first thing I’m going to do is create a helper to deal with the Entity Framework context. If you generate a new Entity Framework database, you’ll get a context that is configured using the App.config file. This is OK, if your application is always going to use the same database. If you need to switch databases, and in this case, we’ll need to use a different database when under unit testing, then you’ll need to control the context parameters. Here’s the helper class that I wrote to handle multiple data sources:
using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;
namespace Helpers
{
public static class EFContextHelper
{
public static string ConnectionString(string connectionName, string databaseName, string modelName, string userName, string password)
{
bool integratedSecurity = (userName == "");
if (UnitTestHelpers.IsInUnitTest)
{
connectionName = "(localdb)\" +
UnitTestHelpers.InstanceName;
integratedSecurity = true;
}
return new EntityConnectionStringBuilder
{
Metadata = "res://*/" + modelName + ".csdl|res://*/" + modelName + ".ssdl|res://*/" + modelName + ".msl",
Provider = "System.Data.SqlClient",
ProviderConnectionString = new
SqlConnectionStringBuilder
{
MultipleActiveResultSets = true,
InitialCatalog = databaseName,
DataSource = connectionName,
IntegratedSecurity = integratedSecurity,
UserID = userName,
Password = password
}.ConnectionString
}.ConnectionString;
}
}
}
The helper class builds the connection string and replaces the one that is defined in the App.config file. There is a check to see if the calling assembly is a unit test assembly, I will use this to override the server name and point to the SQLLocalDB that is defined in the UnitTestHelpers.InstanceName. This will ensure that if an EF context is called inside a method of your program, it will connect to the unit test database if a unit test calls your program.
Once you have created the helpers class. you’ll need to use it inside your context code. To do that, you’ll need to add the code into the Context.tt file (it should be named something like “Model1.Context.tt”). This file (also known as a T4 file), is used to generate the Model1.Context.cs file. Modify the code to look something like this:
#>
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using Helpers;
<#
if (container.FunctionImports.Any())
{
#>
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
<#
}
#>
<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext
{
public <#=code.Escape(container)#>()
: base(EFContextHelper.ConnectionString("SQLSERVERNAME", "sampledata", "Model1", "", ""))
{
First, you’ll need to add the “using Helpers;” line, then you’ll need to add in the EFContextHelper.ConnectionString method as the base class initialization parameter.
Change the SQLSERVERNAME to match your actual MS SQL Server name. Change sampledata to match your actual database name. Change “Model1” to match the name of the edmx file. Finally, you can use a user name and password if you’re not using integrated security on MS SQL Server. In my example, I’m using integrated security. Once you change the tt file, save it and check the matching cs file to make sure there are no syntax errors.
Using the Mapping Generator
Currently the mapping generator is used to generate NHibernate database mappings. If you don’t want the extra code, you can download my project and strip out all the Fluent Nhibernate code (or just leave it for now). I have added a new section that will generate code to create tables that match the tables defined in your database. But first you must run the generator to create the table definitions.
Go to my GitHub account and download the NHibernateMappingGenerator solution. You can find it here. Make sure that the NHibernateMappingGenerator project (which is a Windows Forms project) is set to the startup project. Then run the program and select your SQL Server and database:
If your server doesn’t use integrated security (i.e. you need a user name and password to access your SQL Server), you’ll have to do some open heart surgery (sorry, I haven’t added that feature to this program yet). Once you click the “Generate” button, this program will generate code that can create tables, stored procedures, views and constraints in your SQLLocalDB database instance for unit testing purposes. You should note that your test database will match your production database from the time that you generated this code. If you make database changes to your production system, then you’ll need to regenerate the table definitions.
The Unit Test
I’ll demonstrate one simple unit test. This test isn’t really going to be useful, except it will test the fact that you can insert data into SQLLocalDB and query the data using Entity Framework and it will not corrupt your production data. Here’s the code:
[TestClass]
public class EntityFrameworkTests
{
[TestCleanup]
public void Cleanup()
{
UnitTestHelpers.TruncateData();
}
[TestMethod]
public void TestEntityFrameworkContext()
{
using (var db = new sampledataEntities())
{
Store store = new Store
{
Name = "Test Store",
};
db.Stores.Add(store);
db.SaveChanges();
var resultQuery = (from s in db.Stores
select s).ToList();
Assert.AreEqual(1, resultQuery.Count());
Assert.AreEqual("Test Store", resultQuery[0].Name);
}
}
}
As you can see, using EF from a unit test is just like using it inside your application. Just open a context, insert data, query data, done. The magic is occurring inside the context itself. If you put a break-point inside the EFContextHelper.ConnectionString method, you can see where the connection string is built to point to the SQLLocalDB instance instead of your production database instance (you should always test this to verify that it is functioning correctly).
Generating the Tables
One detail that is buried in this sample code that I haven’t covered yet is how the tables are generated inside the SQLLocalDB instance. In previous blog posts I always used the Fluent NHibernate database generate feature to generate all the tables of the database in the unit test assembly initialize method. If you open the AssemblyCommon.cs file (inside the SampleUnitTests project) and look at the ClassStartInitialize method, you’ll see where I call another UnitTestHelpers method called CreateAllTables. Dig into this method and you’ll see ADO code that creates MS SQL tables for a specified database. This makes the entire unit test helper project agnostic to the type of database you might want to test.
Using This Technique for Other Database Types
The unit test helpers can currently handle EF as well as Fluent NHibernate, but technically, any ORM or direct data access can use this set of helpers. Make sure to create a database connection helper for the database type you will use in your project (or already use in your project). Then you can apply the same UnitTestHelpers.IsInUnitTest check to set the instance to the SQLLocalDB instance. Finally, you can add unit tests for each method in your application, no matter which database context it is using.
As I mentioned in the summary, it would ease your transition to another ORM, assuming that is your goal. Your first step would be to create unit tests around your current objects and methods, using your current database technique, then convert to your target ORM and make sure the unit tests pass.
Getting the Code
You can download the entire solution at my GitHub account: https://github.com/fdecaire/NHibernateMappingGenerator.