Click here to Skip to main content
15,391,989 members
Articles / DevOps / Unit Testing
Posted 17 Sep 2012


27 bookmarked

Two Strategies for Testing Entity Framework - Effort and SQL CE

Rate me:
Please Sign up or sign in to vote.
4.97/5 (14 votes)
19 Sep 2012CPOL6 min read
Two approaches for unit testing Entity Framework
In this article, you will learn about two approaches for unit testing Entity Framework: Mocking in memory by using "Effort", and against a real database using SQLCE


Unit testing business logic that is based on Entity Framework is a difficult task. The aim of Unit Testing is to test the business logic in isolation without dependencies on other components of the system.

The problem with Entity Framework is that it depends on the existence of a real database to run correctly. A common approach for unit testing is to abstract away Entity Framework classes (such as DbContext) and run the business logic under test against mocked objects in memory.

However, there are a few problems with this approach:

  1. Running Linq expressions with mocked objects may produce different results compared to when running against a real database. Some operations may throw exceptions, so a passing Unit test doesn't necessarily mean the code will work in production.
  2. Mocking up test data can be a lot of work. For complex schemas where there are lots of relationships between entity types, these references need to be hand-crafted by setting appropriate Navigation Properties manually.
  3. When running against a real database, the DbSet.Include can configure Entity Framework to automatically load related entities. This method will not work if you are using mocked objects.

This article shows two different approaches for testing Entity Framework that reduces these problems.

1. Effort

This project is an Entity Framework provider that runs in memory. You can still use your DbContext or ObjectContext classes within unit tests, without having to have an actual database.

Many of the features of Entity Framework will still work. Navigation Properties will automatically be loaded with related entities and the DbSet.Include method also works as it would if connected to an real database.

A drawback of this approach is that we are still not running against a real database, but instead a different Entity Framework provider. Some operations do not behave as they would against a real database. In fact, at the time of writing, Effort does not support some operations that are supported by other database providers. (See this issue on CodePlex).

Essentially, this approach helps to solve points 2 and 3 above, but not 1.


This approach is using a real database. This article will show you how to create SQL CE databases on-the-fly to run your tests against. SQL CE also supports Entity Framework Code First, so the database schema is also generated automatically from your entity model.

It's worth pointing out that this approach is likely to be slower than testing in memory since SQL CE is a fully blown database and operations will write to disk as opposed to just working within memory.

An Example Entity Framework Model

In this example, the Entity Model contains "Product" and "Tag" entities, which have a many-to-many relationship between them. This relationship is facilitated with a "ProductTag" entity, that relates a Product with a Tag:

public partial class Product
    public System.Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public Nullable<decimal> Price { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public virtual ICollection<ProductTag> ProductTags { get; set; }

public partial class Tag
    public System.Guid Id { get; set; }
    public string Name { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public virtual ICollection<ProductTag> ProductTags { get; set; }

public partial class ProductTag
    public System.Guid Id { get; set; }
    public System.Guid ProductId { get; set; }
    public System.Guid TagId { get; set; }
    public System.DateTime CreatedDate { get; set; }
    public virtual Product Product { get; set; }
    public virtual Tag Tag { get; set; }

I also have a MyAppContext class that inherits from DbContext.

public partial class MyAppContext : DbContext
    public MyAppContext()
        : base("name=MyAppContext")
        this.Configuration.LazyLoadingEnabled = false;
    public MyAppContext(string connectionString)
        : base(connectionString)
        this.Configuration.LazyLoadingEnabled = false;
    public MyAppContext(DbConnection connection)
        : base(connection, true)
        this.Configuration.LazyLoadingEnabled = false;
    public IDbSet<Product> Products { get; set; }
    public IDbSet<ProductTag> ProductTags { get; set; }
    public IDbSet<Tag> Tags { get; set; }   

Example Business Logic and Unit Test

In order to demonstrate the different test approaches, I need some code to put under test. I have ProductRepository class with a method GetProductsByTagName that gets all Product entities that are tagged with the specified tagName. The class constructor takes a MyAppContext instance, which is the Entity Framework DbContext class.

public class ProductRepository
    private MyAppContext _context;

    public ProductRepository(MyAppContext context)
        _context = context;

    // gets all products that have a matching tag associated with it.
    public IEnumerable<Product> GetProductsByTagName(string tagName)
        // get the products that have a related tag with the specified name
        var products = _context.Products
            .Where(x => x.ProductTags.Any(pt => pt.Tag.Name == tagName))

        return products;

I also have a simple Unit Test that calls this method and tests that the correct Product is returned. In the test below, the _context variable is an instance of MyAppContext. Creating this variable for the two different test approaches is explained in the next section of the article.

public void GetProductsByTagName_Should_ReturnProduct()
    var productRepository = new ProductRepository(_context);

    IEnumerable<Product> products = productRepository.GetProductsByTagName("Tag 1");

    // check the correct product is retrieved
    Assert.AreEqual(1, products.Count());
    Assert.AreEqual("Product 1", products.First().Name);

Testing with Effort

The key to testing will Effort is that we use Effort to create a DbConnection object, which we then use to initialise our MyAppContext class:

DbConnection connection = Effort.DbConnectionFactory.CreateTransient();

var dbContext = new MyAppContext(connection);

Once we have the DbConnection instance, we can use it again and again to create new DbContext instances, which effectively simulate different connections to the same database. This is useful as we can create one connection to populate our database with test data, then create another fresh one to test with.

The DbConnection object is effectively the instance of the database, so if you create a new instance using DbConnectionFactory.CreateTransient(), it will not contain any data that you have added to a different instance. This means you need to hold onto the instance of the DbConnection object for the duration of your test.

The following code shows the Test Initialize method that runs before each test. It creates a new DbConnection object representing a new instance of our database; uses a new DbContext object to add test data to our Entity Mode;, then creates a new DbContext object for use in our test using the same instance of the DbConnection.

private MyAppContext _context;

public void SetupTest()
    // create a new DbConnection using Effort
    DbConnection connection = Effort.DbConnectionFactory.CreateTransient();

    // create the DbContext class using the connection
    using (var context = new MyAppContext(connection))
        // Add test data to the database here
        context.Products.Add(new Product() { Id = ... });
        // ... CODE OMMITTED ...

    // use the same DbConnection object to create the context object the test will use.
    _context = new MyAppContext(connection);

When setting up our test data, it's worth mentioning that there's no need to initialize properties that point to related entites (e.g., Product.ProductTags and Tag.ProductTags). All we need to do is add individual entities and Effort will automatically associate these using the ID values, just as a real database would.

using (MyAppContext context = new MyAppContext(connection))
    // Add 2 Product entities
    context.Products.Add(new Product() { Id = new Guid("CEA4655C-..."), Name = "Product 1", ...
    context.Products.Add(new Product() { Id = new Guid("A4A989A4-..."), Name = "Product 2", ...

    // Add 3 Tag entities
    context.Tags.Add(new Tag() { Id = new Guid("D7FE98A2-..."), Name = "Tag 1", ...
    context.Tags.Add(new Tag() { Id = new Guid("52FEDB17-..."), Name = "Tag 2", ...
    context.Tags.Add(new Tag() { Id = new Guid("45312740-..."), Name = "Tag 3", ...

    // Associate Product 1 with Tag 1
    context.ProductTags.Add(new ProductTag() 
        ProductId = new Guid("CEA4655C-..."), 
        TagId = new Guid("D7FE98A2-...")

    // Associate Product 1 with Tag 3
    context.ProductTags.Add(new ProductTag() 
         ProductId = new Guid("CEA4655C-..."),
         TagId = new Guid("45312740-...")

    // Associate Product 2 with Tag 2
    context.ProductTags.Add(new ProductTag() 
        ProductId = new Guid("A4A989A4-..."),
        TagId = new Guid("52FEDB17-...")

Testing with SQL CE

When testing with SQL CE, we need to create real SQL CE databases for each test, then initialise our MyAppContext instance with a connection to this database. The only way I managed to get this to work is by setting the static DefaultConnectionFactory property of the System.Data.Entity.Database class first, then calling the CreateDatabase() method to generate the database from the Entity Model.

The following implementation of the Test Initialize method can be used to setup our MyAppContext for use in our tests.

public void SetupTest()
    // file path of the database to create
    var filePath = @"C:\code\TestingEf\TestTemp\RealMyAppDb.sdf";

    // delete it if it already exists
    if (File.Exists(filePath))

    // create the SQL CE connection string - this just points to the file path
    string connectionString = "Datasource = "+filePath;

    Database.DefaultConnectionFactory = 
        new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");

    using (var context = new MyAppContext(connectionString))
        // this will create the database with the schema from the Entity Model

    // initialise our DbContext class with the SQL CE connection string, 
    // ready for our tests to use it.
    _context = new MyAppContext(connectionString);

Swapping Between the Two Methods

Chances are, you might want to use Effort for some tests and SQL CE for others. At first, swapping between the two implementations caused problems: Once the SQL CE method was used, subsequent uses of the Effort method in the same test run no longer worked.

The Problem

The call to the static DefaultConnectionFactory property to set its value to the SqlCeConnectionFactory seemed to remove the registration of the Effort provider.

The Solution

Add the following to the App.config of the test project. It adds a registration of the Effort Entity Framework Provider in addition to the one that the Effort library adds itself at runtime. The name and invariant values are different to the values that Effort automatically registers at runtime. This has to be the case to avoid conflicts.

    <add name="Effort Provider Test" 
        description="Effort Provider for unit testing" 

Downloadable Sample Project

Take a look at the sample project for working examples. I've wrapped up the two testing approaches into two different classes:

  • TestingEf.Data.Tests.TestDatabaseStrategies.EffortDatabaseStrategy
  • TestingEf.Data.Tests.TestDatabaseStrategies.SqlCeDatabaseStrategy

These classes are referenced from the tests in the TestingEf.Data.Tests.ProductRepositoryTests namespace which test the ProductRepository in more depth than covered in this article. Within these tests, there is a failing test that highlights how Effort does not support String.IsNullOrEmpty within Entity Framework queries when SQL CE (and SQL Server) does.

The sample project also contains an IMyAppContext interface, which is reference throughout the code instead of using the concrete MyAppContext implementation directly. Use of the interface is not necessary to explain the testing methods in this article, but it's good practice for other components of the system to use interfaces as this helps with mocking when testing different components of the system.

This interface is automatically generated from the T4 template. This is a modified version of the EF 5 DbContext Generator T4 which generates the classes of the Entity Model as a DbContext class and POCO objects from an EDMX file. More information about using the DbContext generator can be found at this link.


  • 17th September, 2012: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

David Bywaters
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

Suggestionstatement missing. Pin
andresfalcon14-Feb-18 10:35
Memberandresfalcon14-Feb-18 10:35 
QuestionNice article on repository testing Pin
Mou_kol25-Aug-17 2:51
MemberMou_kol25-Aug-17 2:51 
QuestionSwapping not working with EF 6.1 Pin
MEMark227-Apr-14 10:31
MemberMEMark227-Apr-14 10:31 
AnswerRe: Swapping not working with EF 6.1 Pin
john_17264-Sep-15 13:11
Memberjohn_17264-Sep-15 13:11 
What kinds of errors are you getting? It is working for me using EF 6.1.3.

modified 8-Sep-15 11:22am.

GeneralMy vote of 5 Pin
Monjurul Habib27-Dec-13 11:56
professionalMonjurul Habib27-Dec-13 11:56 
QuestionSample does not work Pin
gilligan1283-Oct-12 6:17
Membergilligan1283-Oct-12 6:17 
QuestionThanks for the post Pin
Tamas Flamich20-Sep-12 22:35
MemberTamas Flamich20-Sep-12 22:35 
AnswerRe: Thanks for the post Pin
David Bywaters21-Sep-12 4:35
MemberDavid Bywaters21-Sep-12 4:35 
GeneralMy vote of 5 Pin
Kanasz Robert20-Sep-12 1:35
professionalKanasz Robert20-Sep-12 1:35 
QuestionThird alternative Pin
Rudi Breedenraedt19-Sep-12 11:51
MemberRudi Breedenraedt19-Sep-12 11:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.