Skip to main content

Unit Testing Entity Framework with Effort

Previous posts in this series


In my previous post I discussed steps I've been taking with recent application to move more logic and behaviour into a richer domain model. This has a number of advantages, a significant one being ease of testing of this behaviour. As this is coded as methods on a standalone model class, it's very easy to test due to it's lack of dependencies. Nothing needs to be mocked or stubbed; an instance can just be instantiated in the test method and the appropriate methods called and results asserted.

Given the CQRS style approach I've taken elsewhere in the applications, MVC controllers are fairly light and have less value for testing. The main part of the application where logic lies is in the individual query and command handlers. These have a tight dependency on Entity Framework though so are significantly harder to test.

Approaches to Unit Testing Entity Framework

One track I expect a lot of developers have started down is to attempt to mock out the EF context, allowing you to return a known in memory collection or instance instead of hitting the database for your tests. EF 6 provides some good support for this. This is certainly fast, but the downside with this is that querying using LINQ to Objects is not the same as querying LINQ to Entities. There's a good degree of overlap so still some value in this, but writing tests this way runs the risk of having tests that pass in your tests but fail in the application.

Another approach is to simply use the database itself. Most people wouldn't class this a unit test any more, rather an integration one, as it suffers from two downsides. One is that it's going to be a lot slower than using an in-memory data store and the second that it's hard to ensure your data remains consistent. If one test changes the data, you have to make sure you have a fresh schema and data situation restored for further tests, which can take quite a bit of effort to maintain.

Introducing Effort

Effort is an open-source provider that generates an in-memory database from your EF model, and accurately represents (almost) all EF querying operations. Thus in each test you can instantiate a clean data store and run your tests against it. In my case this meant asserting that my queries return the correct results and view models, and commands persist the data as expected.

The big advantage here therefore is removing the brittleness of the tests in ensuring you have a known base schema and data to start each test. It's also fairly fast - I say fairly, as I still find half a second or so for each test, which isn't much but adds up of course if you have a lot.

One other small downside is the "almost" note above. There are some EF operations you can run - within SqlFunctions for example for date operations - that are SQL Server specfic. Effort attempts to remain database agnostic, so can't handle the use of these functions. I get round that by passing a flag indicating whether SQL specific functions are available to the query handler - if they are, as in the application itself, they are used. In the test they are not. So there's a small difference in behaviour between my tests and the real-world application - but for my application at least this is pretty insignificant.

Effort Example

There's good documentation on the Effort Codeplex site for it's set up and use, including seeding data from code or CSV files. I'll just note here some aspects I had to work around slightly differently - in particular as I am using ASP.Net Identity, which means the constructors available for "standard" EF aren't available and some modification is required. Many thanks to the author of the library for his help with this.

Firstly, here's an example of a test. This one is testing a query handler - confirming that given a particular query, the correct view model is populated and returned.

public void SurveyQuestionViewModelQueryHandler_WithValidQuery_ReturnsExpectedResults()
    // Arrange
    var responseId = CreateAndPersistResponse();

    var handler = new QuestionViewModelQueryHandler(Context);
    handler.SqlSpecificFunctionsAvailable = false;
    var worker = Context.OrganisationWorkers.Single(x => x.LastName == "Worker");
    var questionId = Context.Questions.Single(x => x.Code == "A6").Id;

    var query = new QuestionViewModelQuery {
        UserId = worker.Id,
        ResponseId = responseId,
        QuestionId = questionId,

    // Act
    var result = handler.Retrieve(query).Result;

    // Assert
    Assert.AreEqual(questionId, result.QuestionId);
    Assert.AreEqual("Which of these are vegatables?", result.QuestionText);
    Assert.AreEqual("Section 1", result.SectionName);
    Assert.AreEqual(4, result.AnswerOptions.Count());
    Assert.AreEqual(0, result.SurveyProgressPercent);
    Assert.AreEqual(42, result.SectionProgressPercent);

The first step is to set up the in-memory context using the Effort library via the SetUpContextAndTestData() method, which I'll expand on in a moment. This creates the schema and a set of base data required for all tests. After that we call a helper method to instantiate a single survey response object (an entity in my application) that we need for this specific test.

The rest of the Arrange part of the test is involved with instantiating a handler object, setting the property on the handler that indicates of SQL specific functions can be called, and looking up some values from the in-memory database to create a query object.

The single line in the Act simply calls the Retrieve method on the handler and retrieves the result. In the Assert section we call several asserts, to ensure the resulting view model is populated as we are expecting.

Test Setup

Going back to the arrange steps, the following code is used to instantiate the in-memory database using the Effort library.

protected void SetUpContextAndTestData()
    TestDataSeeder.SeedData(Context, true);

private void InitContext()
    var dataFolder = AppDomain.CurrentDomain.BaseDirectory;

    var connection = Effort.DbConnectionFactory.CreateTransient();
    var dummyContext = new ApplicationDbContext();
    var builder = new DbModelBuilder();

    var m = typeof(ApplicationDbContext).GetMethod("ConfigureModel", BindingFlags.NonPublic | BindingFlags.Instance);
    m.Invoke(dummyContext, new object[] { builder, false });

    var model = builder.Build(connection).Compile();

    Context = new ApplicationDbContext(connection, model, false);
    Context.Configuration.AutoDetectChangesEnabled = true;

The call to InitContext sets up the schema using a variation of the standard Effort instantiation to work with the constructors available in ASP.Net Identity. A dummy context is first created and then the Effor in-memory model is built from that. Please note if you aren't using ASP.Net Identity then you should follow the set-up code as detailed on the Codeplex site.

We then make a call to a method responsible for seeding the data. This actually leverages the existing code for seeding data following an EF migration. It passes a flag to indicate to the seeding method whether on not to call any SQL server specific migrations. For example I had some code to override conventions for various date fields to use the smalldatetime data type - as this is SQL specific, Effort won't be able to work with this. But as this setting isn't relevant for tests, it can safely be ignored.


I'd certainly recommend developers looking to test EF methods take a look at Effort. Pun intended, in using it there's not much effort involved in getting the parts of your application that traditionally would be quite hard to test, under your unit test coverage.