[c#] How are people unit testing with Entity Framework 6, should you bother?

I am just starting out with Unit testings and TDD in general. I have dabbled before but now I am determined to add it to my workflow and write better software.

I asked a question yesterday that kind of included this, but it seems to be a question on its own. I have sat down to start implementing a service class that I will use to abstract away the business logic from the controllers and map to specific models and data interactions using EF6.

The issue is I have roadblocked myself already because I didn't want to abstract EF away in a repository (it will still be available outside the services for specific queries, etc) and would like to test my services (EF Context will be used).

Here I guess is the question, is there a point to doing this? If so, how are people doing it in the wild in light of the leaky abstractions caused by IQueryable and the many great posts by Ladislav Mrnka on the subject of unit testing not being straightforward because of the differences in Linq providers when working with an in memory implementation as apposed to a specific database.

The code I want to test seems pretty simple. (this is just dummy code to try and understand what i am doing, I want to drive the creation using TDD)

Context

public interface IContext
{
    IDbSet<Product> Products { get; set; }
    IDbSet<Category> Categories { get; set; }
    int SaveChanges();
}

public class DataContext : DbContext, IContext
{
    public IDbSet<Product> Products { get; set; }
    public IDbSet<Category> Categories { get; set; }

    public DataContext(string connectionString)
                : base(connectionString)
    {

    }
}

Service

public class ProductService : IProductService
{
    private IContext _context;

    public ProductService(IContext dbContext)
    {
        _context = dbContext;
    }

    public IEnumerable<Product> GetAll()
    {
        var query = from p in _context.Products
                    select p;

        return query;
    }
}

Currently I am in the mindset of doing a few things:

1. Mocking EF Context with something like this approach- Mocking EF When Unit Testing or directly using a mocking framework on the interface like moq - taking the pain that the unit tests may pass but not necessarily work end to end and back them up with Integration tests?
2. Maybe using something like Effort to mock EF - I have never used it and not sure if anyone else is using it in the wild?
3. Not bother testing anything that simply calls back to EF - so essentially service methods that call EF directly (getAll etc) are not unit tested but just integration tested?

Anyone out there actually doing this out there without a Repo and having success?

In order to unit test code that relies on your database you need to setup a database or mock for each and every test.

1. Having a database (real or mocked) with a single state for all your tests will bite you quickly; you cannot test all records are valid and some aren't from the same data.
2. Setting up an in-memory database in a OneTimeSetup will have issues where the old database is not cleared down before the next test starts up. This will show as tests working when you run them individually, but failing when you run them all.
3. A Unit test should ideally only set what affects the test

I am working in an application that has a lot of tables with a lot of connections and some massive Linq blocks. These need testing. A simple grouping missed, or a join that results in more than 1 row will affect results.

To deal with this I have setup a heavy Unit Test Helper that is a lot of work to setup, but enables us to reliably mock the database in any state, and running 48 tests against 55 interconnected tables, with the entire database setup 48 times takes 4.7 seconds.

Here's how:

1. In the Db context class ensure each table class is set to virtual

   public virtual DbSet<Branch> Branches { get; set; }
   public virtual DbSet<Warehouse> Warehouses { get; set; }
   

2. In a UnitTestHelper class create a method to setup your database. Each table class is an optional parameter. If not supplied, it will be created through a Make method

   internal static Db Bootstrap(bool onlyMockPassedTables = false, List<Branch> branches = null, List<Products> products = null, List<Warehouses> warehouses = null)
   {
       if (onlyMockPassedTables == false) {
           branches ??= new List<Branch> { MakeBranch() };
           warehouses ??= new List<Warehouse>{ MakeWarehouse() };
       }
   

3. For each table class, each object in it is mapped to the other lists

       branches?.ForEach(b => {
           b.Warehouse = warehouses.FirstOrDefault(w => w.ID == b.WarehouseID);
       });
   
       warehouses?.ForEach(w => {
           w.Branches = branches.Where(b => b.WarehouseID == w.ID);
       });
   

4. And add it to the DbContext

        var context = new Db(new DbContextOptionsBuilder<Db>().UseInMemoryDatabase(Guid.NewGuid().ToString()).Options);
        context.Branches.AddRange(branches);
        context.Warehouses.AddRange(warehouses);
        context.SaveChanges();
        return context;
    }
   

5. Define a list of IDs to make is easier to reuse them and make sure joins are valid

    internal const int BranchID = 1;
    internal const int WarehouseID = 2;
   

6. Create a Make for each table to setup the most basic, but connected version it can be

    internal static Branch MakeBranch(int id = BranchID, string code = "The branch", int warehouseId = WarehouseID) => new Branch { ID = id, Code = code, WarehouseID = warehouseId };
    internal static Warehouse MakeWarehouse(int id = WarehouseID, string code = "B", string name = "My Big Warehouse") => new Warehouse { ID = id, Code = code, Name = name };
   

It's a lot of work, but it only needs doing once, and then your tests can be very focused because the rest of the database will be setup for it.

[Test]
[TestCase(new string [] {"ABC", "DEF"}, "ABC", ExpectedResult = 1)]
[TestCase(new string [] {"ABC", "BCD"}, "BC", ExpectedResult = 2)]
[TestCase(new string [] {"ABC"}, "EF", ExpectedResult = 0)]
[TestCase(new string[] { "ABC", "DEF" }, "abc", ExpectedResult = 1)]
public int Given_SearchingForBranchByName_Then_ReturnCount(string[] codesInDatabase, string searchString)
{
    // Arrange
    var branches = codesInDatabase.Select(x => UnitTestHelpers.MakeBranch(code: $"qqqq{x}qqq")).ToList();
    var db = UnitTestHelpers.Bootstrap(branches: branches);
    var service = new BranchService(db);

    // Act
    var result = service.SearchByName(searchString);

    // Assert
    return result.Count();
}

I have fumbled around sometime to reach these considerations:

1- If my application access the database, why the test should not? What if there is something wrong with data access? The tests must know it beforehand and alert myself about the problem.

2- The Repository Pattern is somewhat hard and time consuming.

So I came up with this approach, that I don't think is the best, but fulfilled my expectations:

Use TransactionScope in the tests methods to avoid changes in the database.

To do it it's necessary:

1- Install the EntityFramework into the Test Project. 2- Put the connection string into the app.config file of Test Project. 3- Reference the dll System.Transactions in Test Project.

The unique side effect is that identity seed will increment when trying to insert, even when the transaction is aborted. But since the tests are made against a development database, this should be no problem.

Sample code:

[TestClass]
public class NameValueTest
{
    [TestMethod]
    public void Edit()
    {
        NameValueController controller = new NameValueController();

        using(var ts = new TransactionScope()) {
            Assert.IsNotNull(controller.Edit(new Models.NameValue()
            {
                NameValueId = 1,
                name1 = "1",
                name2 = "2",
                name3 = "3",
                name4 = "4"
            }));

            //no complete, automatically abort
            //ts.Complete();
        }
    }

    [TestMethod]
    public void Create()
    {
        NameValueController controller = new NameValueController();

        using (var ts = new TransactionScope())
        {
            Assert.IsNotNull(controller.Create(new Models.NameValue()
            {
                name1 = "1",
                name2 = "2",
                name3 = "3",
                name4 = "4"
            }));

            //no complete, automatically abort
            //ts.Complete();
        }
    }
}

There is Effort which is an in memory entity framework database provider. I've not actually tried it... Haa just spotted this was mentioned in the question!

Alternatively you could switch to EntityFrameworkCore which has an in memory database provider built-in.

https://blog.goyello.com/2016/07/14/save-time-mocking-use-your-real-entity-framework-dbcontext-in-unit-tests/

https://github.com/tamasflamich/effort

I used a factory to get a context, so i can create the context close to its use. This seems to work locally in visual studio but not on my TeamCity build server, not sure why yet.

return new MyContext(@"Server=(localdb)\mssqllocaldb;Database=EFProviders.InMemory;Trusted_Connection=True;");

So here's the thing, Entity Framework is an implementation so despite the fact that it abstracts the complexity of database interaction, interacting directly is still tight coupling and that's why it's confusing to test.

Unit testing is about testing the logic of a function and each of its potential outcomes in isolation from any external dependencies, which in this case is the data store. In order to do that, you need to be able to control the behavior of the data store. For example, if you want to assert that your function returns false if the fetched user doesn't meet some set of criteria, then your [mocked] data store should be configured to always return a user that fails to meet the criteria, and vice versa for the opposite assertion.

With that said, and accepting the fact that EF is an implementation, I would likely favor the idea of abstracting a repository. Seem a bit redundant? It's not, because you are solving a problem which is isolating your code from the data implementation.

In DDD, the repositories only ever return aggregate roots, not DAO. That way, the consumer of the repository never has to know about the data implementation (as it shouldn't) and we can use that as an example of how to solve this problem. In this case, the object that is generated by EF is a DAO and as such, should be hidden from your application. This another benefit of the repository that you define. You can define a business object as its return type instead of the EF object. Now what the repo does is hide the calls to EF and maps the EF response to that business object defined in the repos signature. Now you can use that repo in place of the DbContext dependency that you inject into your classes and consequently, now you can mock that interface to give you the control that you need in order to test your code in isolation.

It's a bit more work and many thumb their nose at it, but it solves a real problem. There's an in-memory provider that was mentioned in a different answer that could be an option (I have not tried it), and its very existence is evidence of the need for the practice.

I completely disagree with the top answer because it sidesteps the real issue which is isolating your code and then goes on a tangent about testing your mapping. By all means test your mapping if you want to, but address the actual issue here and get some real code coverage.

If you want to unit test code then you need to isolate your code you want to test (in this case your service) from external resources (e.g. databases). You could probably do this with some sort of in-memory EF provider, however a much more common way is to abstract away your EF implementation e.g. with some sort of repository pattern. Without this isolation any tests you write will be integration tests, not unit tests.

As for testing EF code - I write automated integration tests for my repositories that write various rows to the database during their initialization, and then call my repository implementations to make sure that they behave as expected (e.g. making sure that results are filtered correctly, or that they are sorted in the correct order).

These are integration tests not unit tests, as the tests rely on having a database connection present, and that the target database already has the latest up-to-date schema installed.

I would not unit test code I don't own. What are you testing here, that the MSFT compiler works?

That said, to make this code testable, you almost HAVE to make your data access layer separate from your business logic code. What I do is take all of my EF stuff and put it in a (or multiple) DAO or DAL class which also has a corresponding interface. Then I write my service which will have the DAO or DAL object injected in as a dependency (constructor injection preferably) referenced as the interface. Now the part that needs to be tested (your code) can easily be tested by mocking out the DAO interface and injecting that into your service instance inside your unit test.

//this is testable just inject a mock of IProductDAO during unit testing
public class ProductService : IProductService
{
    private IProductDAO _productDAO;

    public ProductService(IProductDAO productDAO)
    {
        _productDAO = productDAO;
    }

    public List<Product> GetAllProducts()
    {
        return _productDAO.GetAll();
    }

    ...
}

I would consider live Data Access Layers to be part of integration testing, not unit testing. I have seen guys run verifications on how many trips to the database hibernate makes before, but they were on a project that involved billions of records in their datastore and those extra trips really mattered.

I want to share an approach commented about and briefly discussed but show an actual example that I am currently using to help unit test EF-based services.

First, I would love to use the in-memory provider from EF Core, but this is about EF 6. Furthermore, for other storage systems like RavenDB, I'd also be a proponent of testing via the in-memory database provider. Again--this is specifically to help test EF-based code without a lot of ceremony.

Here are the goals I had when coming up with a pattern:

• It must be simple for other developers on the team to understand
• It must isolate the EF code at the barest possible level
• It must not involve creating weird multi-responsibility interfaces (such as a "generic" or "typical" repository pattern)
• It must be easy to configure and setup in a unit test

I agree with previous statements that EF is still an implementation detail and it's okay to feel like you need to abstract it in order to do a "pure" unit test. I also agree that ideally, I would want to ensure the EF code itself works--but this involves a sandbox database, in-memory provider, etc. My approach solves both problems--you can safely unit test EF-dependent code and create integration tests to test your EF code specifically.

The way I achieved this was through simply encapsulating EF code into dedicated Query and Command classes. The idea is simple: just wrap any EF code in a class and depend on an interface in the classes that would've originally used it. The main issue I needed to solve was to avoid adding numerous dependencies to classes and setting up a lot of code in my tests.

This is where a useful, simple library comes in: Mediatr. It allows for simple in-process messaging and it does it by decoupling "requests" from the handlers that implement the code. This has an added benefit of decoupling the "what" from the "how". For example, by encapsulating the EF code into small chunks it allows you to replace the implementations with another provider or totally different mechanism, because all you are doing is sending a request to perform an action.

Utilizing dependency injection (with or without a framework--your preference), we can easily mock the mediator and control the request/response mechanisms to enable unit testing EF code.

First, let's say we have a service that has business logic we need to test:

public class FeatureService {

  private readonly IMediator _mediator;

  public FeatureService(IMediator mediator) {
    _mediator = mediator;
  }

  public async Task ComplexBusinessLogic() {
    // retrieve relevant objects

    var results = await _mediator.Send(new GetRelevantDbObjectsQuery());
    // normally, this would have looked like...
    // var results = _myDbContext.DbObjects.Where(x => foo).ToList();

    // perform business logic
    // ...    
  }
}

Do you start to see the benefit of this approach? Not only are you explicitly encapsulating all EF-related code into descriptive classes, you are allowing extensibility by removing the implementation concern of "how" this request is handled--this class doesn't care if the relevant objects come from EF, MongoDB, or a text file.

Now for the request and handler, via MediatR:

public class GetRelevantDbObjectsQuery : IRequest<DbObject[]> {
  // no input needed for this particular request,
  // but you would simply add plain properties here if needed
}

public class GetRelevantDbObjectsEFQueryHandler : IRequestHandler<GetRelevantDbObjectsQuery, DbObject[]> {
  private readonly IDbContext _db;

  public GetRelevantDbObjectsEFQueryHandler(IDbContext db) {
    _db = db;
  }

  public DbObject[] Handle(GetRelevantDbObjectsQuery message) {
    return _db.DbObjects.Where(foo => bar).ToList();
  }
}

As you can see, the abstraction is simple and encapsulated. It's also absolutely testable because in an integration test, you could test this class individually--there are no business concerns mixed in here.

So what does a unit test of our feature service look like? It's way simple. In this case, I'm using Moq to do mocking (use whatever makes you happy):

[TestClass]
public class FeatureServiceTests {

  // mock of Mediator to handle request/responses
  private Mock<IMediator> _mediator;

  // subject under test
  private FeatureService _sut;

  [TestInitialize]
  public void Setup() {

    // set up Mediator mock
    _mediator = new Mock<IMediator>(MockBehavior.Strict);

    // inject mock as dependency
    _sut = new FeatureService(_mediator.Object);
  }

  [TestCleanup]
  public void Teardown() {

    // ensure we have called or expected all calls to Mediator
    _mediator.VerifyAll();
  }

  [TestMethod]
  public void ComplexBusinessLogic_Does_What_I_Expect() {
    var dbObjects = new List<DbObject>() {
      // set up any test objects
      new DbObject() { }
    };

    // arrange

    // setup Mediator to return our fake objects when it receives a message to perform our query
    // in practice, I find it better to create an extension method that encapsulates this setup here
    _mediator.Setup(x => x.Send(It.IsAny<GetRelevantDbObjectsQuery>(), default(CancellationToken)).ReturnsAsync(dbObjects.ToArray()).Callback(
    (GetRelevantDbObjectsQuery message, CancellationToken token) => {
       // using Moq Callback functionality, you can make assertions
       // on expected request being passed in
       Assert.IsNotNull(message);
    });

    // act
    _sut.ComplexBusinessLogic();

    // assertions
  }

}

You can see all we need is a single setup and we don't even need to configure anything extra--it's a very simple unit test. Let's be clear: This is totally possible to do without something like Mediatr (you would simply implement an interface and mock it for tests, e.g. IGetRelevantDbObjectsQuery), but in practice for a large codebase with many features and queries/commands, I love the encapsulation and innate DI support Mediatr offers.

If you're wondering how I organize these classes, it's pretty simple:

- MyProject
  - Features
    - MyFeature
      - Queries
      - Commands
      - Services
      - DependencyConfig.cs (Ninject feature modules)

Organizing by feature slices is beside the point, but this keeps all relevant/dependent code together and easily discoverable. Most importantly, I separate the Queries vs. Commands--following the Command/Query Separation principle.

This meets all my criteria: it's low-ceremony, it's easy to understand, and there are extra hidden benefits. For example, how do you handle saving changes? Now you can simplify your Db Context by using a role interface (IUnitOfWork.SaveChangesAsync()) and mock calls to the single role interface or you could encapsulate committing/rolling back inside your RequestHandlers--however you prefer to do it is up to you, as long as it's maintainable. For example, I was tempted to create a single generic request/handler where you'd just pass an EF object and it would save/update/remove it--but you have to ask what your intention is and remember that if you wanted to swap out the handler with another storage provider/implementation, you should probably create explicit commands/queries that represent what you intend to do. More often than not, a single service or feature will need something specific--don't create generic stuff before you have a need for it.

There are of course caveats to this pattern--you can go too far with a simple pub/sub mechanism. I've limited my implementation to only abstracting EF-related code, but adventurous developers could start using MediatR to go overboard and message-ize everything--something good code review practices and peer reviews should catch. That's a process issue, not an issue with MediatR, so just be cognizant of how you're using this pattern.

You wanted a concrete example of how people are unit testing/mocking EF and this is an approach that's working successfully for us on our project--and the team is super happy with how easy it is to adopt. I hope this helps! As with all things in programming, there are multiple approaches and it all depends on what you want to achieve. I value simplicity, ease of use, maintainability, and discoverability--and this solution meets all those demands.

I like to separate my filters from other portions of the code and test those as I outline on my blog here http://coding.grax.com/2013/08/testing-custom-linq-filter-operators.html

That being said, the filter logic being tested is not identical to the filter logic executed when the program is run due to the translation between the LINQ expression and the underlying query language, such as T-SQL. Still, this allows me to validate the logic of the filter. I don't worry too much about the translations that happen and things such as case-sensitivity and null-handling until I test the integration between the layers.

In short I would say no, the juice is not worth the squeeze to test a service method with a single line that retrieves model data. In my experience people who are new to TDD want to test absolutely everything. The old chestnut of abstracting a facade to a 3rd party framework just so you can create a mock of that frameworks API with which you bastardise/extend so that you can inject dummy data is of little value in my mind. Everyone has a different view of how much unit testing is best. I tend to be more pragmatic these days and ask myself if my test is really adding value to the end product, and at what cost.

Effort Experience Feedback here

After a lot of reading I have been using Effort in my tests: during the tests the Context is built by a factory that returns a in memory version, which lets me test against a blank slate each time. Outside of the tests, the factory is resolved to one that returns the whole Context.

However i have a feeling that testing against a full featured mock of the database tends to drag the tests down; you realize you have to take care of setting up a whole bunch of dependencies in order to test one part of the system. You also tend to drift towards organizing together tests that may not be related, just because there is only one huge object that handles everything. If you don't pay attention, you may find yourself doing integration testing instead of unit testing

I would have prefered testing against something more abstract rather than a huge DBContext but i couldn't find the sweet spot between meaningful tests and bare-bone tests. Chalk it up to my inexperience.

So i find Effort interesting; if you need to hit the ground running it is a good tool to quickly get started and get results. However i think that something a bit more elegant and abstract should be the next step and that is what I am going to investigate next. Favoriting this post to see where it goes next :)

Edit to add: Effort do take some time to warm up, so you're looking at approx. 5 seconds at test start up. This may be a problem for you if you need your test suite to be very efficient.

Edited for clarification:

I used Effort to test a webservice app. Each message M that enters is routed to a IHandlerOf<M> via Windsor. Castle.Windsor resolves the IHandlerOf<M> which resovles the dependencies of the component. One of these dependencies is the DataContextFactory, which lets the handler ask for the factory

In my tests I instantiate the IHandlerOf component directly, mock all the sub-components of the SUT and handles the Effort-wrapped DataContextFactory to the handler.

It means that I don't unit test in a strict sense, since the DB is hit by my tests. However as I said above it let me hit the ground running and I could quickly test some points in the application

It is important to test what you are expecting entity framework to do (i.e. validate your expectations). One way to do this that I have used successfully, is using moq as shown in this example (to long to copy into this answer):

https://docs.microsoft.com/en-us/ef/ef6/fundamentals/testing/mocking

However be careful... A SQL context is not guaranteed to return things in a specific order unless you have an appropriate "OrderBy" in your linq query, so its possible to write things that pass when you test using an in-memory list (linq-to-entities) but fail in your uat / live environment when (linq-to-sql) gets used.