How to unit test Entity Framework?

Question

I'm trying to test my code using EntityFramework code first. In order to make it testable and to allow isolation testing, I created an interface which my DbContext implements. I'm not testing the DbContext class - I'm going to assume EF code works as expected.

Now, consider the following method:

public IEnumerable<User> GetOddId()
{
    return context_.Users.Where((u, i) => i % 2 == 1).AsEnumerable();
}

This method will pass with my mock FakeDbSet (because it would use the in-memory LINQ provider) whereas it would fail with an Exception when using the EF/LINQ to SQL drivers.

Would you leave it as it is and hope people know enough not to write such queries? Would you give up isolation testing and test on an actual db?

Would the LocalDb with DataMigrations (perhaps with appropriate seeds) help with testing on an actual db?

Please justify the answer(s).

TLDR: How to test EntityFramework code, considering the differences between in-memory LINQ and SQL LINQ?

Much later edit: I've since found a very good framework that does exactly what I need. I wrote a blog post about unit testing with Effort. Also please note all this might not be needed in the upcoming EF6, which promises a some unit testing features.

Answer 1

We use SQLite's in-memory databases for this purpose. They are extremely quick to create, query and tear down and barely have any impact on overall test speed. Once you've set yourself up a test framework to create a database and inject data, tests are quick to write.

Of course, SQLite is a much simpler database than most, so complex queries may fail to translate to its version of SQL, but for testing 90% of cases, it works well.

Do these tests constitute integration tests? I don't think so. They are still only testing one unit of your code, namely the bit that generates a LINQ query. You're testing for two things: 1) that the query returns the correct data (but you could check this using an in-memory collection as you stated), and 2) that the query can be translated into valid SQL by Entity Framework. The only real way to test the latter is to fire the query at a real Entity Framework but with a stubbed database.

Whilst you could argue that a true unit test should test just the output of your code (i.e. parse and check the expression tree that has been generated), as well as being harder to write, it doesn't really prove anything. If, for example, you modify the code to generate an inner join instead of a subquery, would you want the test to break? Only if it returns different results, I would have thought.

Answer 2

Where I work, we have a dev/beta/production SQL server. Sometimes we'll create tests against beta and seed test data (e.g. insert before testing specific selects and such) before executing tests on an actual database. People will draw a distinction between unit and integration testing, but it at least lets us test our logic, which is better than just crossing fingers and hoping for the best at the data-access layer.

What good is an in-memory provider that's easy to test for but doesn't actually represent the live system in some important cases?

EDIT: We don't use EF, btw...