Revisiting – how to make your EF queries testable

So, some time back I spent some time agonizing around testability of code which has to talk to a database. I now think I have found a somewhat reasonable answer, which assumes that you are using some kind of LINQ query relational database mapper like EntityFramework which my samples are based on.

It has these basic features.
-Your queries are unit testable independent of a database.
-Your queries are usable/testable with a database, if you want to.
-Your queries aren’t automatically easy to mock out – at least the way I am doing it now. Maybe that can be improved, however the good news is that frequently you can just mock out your DbContext instead
-It leads you in a nice direction of keeping the fully power of IQueryable at your fingertips.

What do I mean by the last point?

Well let’s see. I’m sure you’ve seen an interface like this:

interface IPackagesService {

     IList<PackageVersion> GetPackageVersionsById(string id);
     IList<Package> GetPackagesByAuthor(string author);

This is basically done because it is a solution to a problem: you want to make it possible to mock out your queries, and make some business logic unit testable. But what you are also doing is trying to drink a firehose through a couple straws. The firehose is the full power of SQL. The straw is a (string author) filter on one hand, and a list of packages with a predetermined set of eagerly-populated properties on the other.

Very soon these restrictions start to feel chafing, which leads the interface of your functions to mutate into more and more specialized and arcane looking function signatures:

interface IPackagesService {
     IList<Package> GetPackagesByAuthor(string author, bool withAllPackageVersions = false);

If you continue this and take it to its full extreme, you end up building a magical parameters object with many properties which you will set to try to restore to yourself the full power of LINQ, which you took away by going through the (string author) straw in the first place.

So what do you do instead? Anyway, I’ve basically given away what I think the answer already: IQueryable all the things!

class IDbContext {
    IQueryable<Package> Packages { get; set; }

class PackageAndVersion {
     Package Package { get; set; }
     PackageVersion LatestVersion { get; set; }

static class PackageQueries {
    static IQueryable<Package> ByAuthor(this IQueryable<Package> set, string author);
    static IQueryable<Packages> ById(this IQueryable<Package> set, string author);
    static IQueryable<PackageVersions> SelectAllVersions(this IQueryable<Package> set);
    static IQueryable<PackageAndVersion> SelectPackageWithLatestVersion(this IQueryable<Package> set);

When you consume it you are now writing:

var allUsersPackageVersions = _context.Packages.ByAuthor(user).SelectAllVersions();
var allVersionsOfPackage = _context.Packages.ById(id).SelectAllVersions();

and so on…

By the way you’ll notice the implementation of any of those methods is trivial.

ById(this IQueryable<Package> set, string author) { return set.Where(p =>p.Author.Name == author); }
SelectAllVersions(this IQueryable<Package> set) { return set.SelectMany(p => p.PackageVersions); }

So you’ll probably say “It’ so simple. How could this possibly add any value?” Well, first of all, simplicity is kind of good in and of itself. “But I thought you were saying this is testable? How?” Now that’s a good question!

Here are your options:
-Now it’s dead simple to test your query in isolation against any IQueryable!
-Testing your business logic combined with your real query is also easy. Just mocking out DbContext.Packages with a stub that returns a collection of packages. This is basically the ‘Database Faker’ strategy, when that is a good fit for what you want to test.
-You could also fake out your query the traditional way by having hookable methods that wrap the query at the top level, if you want to… I don’t think you do though. What you really want is…
-Or you could just do another magical thing: stub out your queries on demand and just give the results you want.

Aha, this is a new idea to me! But how is that possible…? Well, luckily IQueryable is all virtual calls, so it’s mockable…

So now we can mock out out context and the query in some fell swoops by doing this:

context = A.Fake<DbContext>(); 
a.CallTo(() => context.Packages).Returns(new QueryFaker<Package>(
   new string[] { “author1”, “author2”, “author3” }// expected results of the query at the point the query is enumerated – whatever the heck type you want – just put the expected results here

var result = context.Packages.Top3().SelectAuthors().Select(author =>;
Assert.Equal(“author1”, result[0]);(expression, _results); } public TResult Execute(Expression expression) { return (TResult)_results; } public object Execute(Expression expression) { return _results; } }

Dear reader,
The conclusion in your mind reading this should be
-YES! You really don’t have to insert interfaces between your classes just so that your queries are mockable.
-YES! You can still test your queries!

Which of the patterns discussed in the previous post does this come the closest to? You can debate, but I would say this is just a very lightweight variant/factoring of the QueryPattern, which in its more heavyweight version that I have seen before is similar in spirit, but requires you to model each query as a dependency object, and with the interesting bias that *not* mocking out the query is the ‘defaultest’ thing to do. You have to go the extra mile to mock out a query, and you’ll do it when you think it’s easier than providing fake data in the mock db.


PS, have I tried these ideas at more than toy implementation scale? Not yet. Smile

Appendix: Bare bones implementation of QueryFaker:

class Query : IQueryable, IQueryable, IEnumerable, IEnumerable, IOrderedQueryable, IOrderedQueryable { public IQueryProvider Provider { get; protected set; } Expression expression; public Query() { this.expression = Expression.Constant(this); } public Query(Expression expression) { this.expression = expression; } Expression IQueryable.Expression { get { return this.expression; } } Type IQueryable.ElementType { get { return typeof(T); } } public IEnumerator GetEnumerator() { return ((IEnumerable)this.Provider.Execute(this.expression)).GetEnumerator(); } IEnumerator IEnumerable.GetEnumerator() { return ((IEnumerable)this.Provider.Execute(this.expression)).GetEnumerator(); } } class QueryFaker : Query, IQueryProvider { public object _results; public QueryFaker(object results) : base () { base.Provider = this; this._results = results; } public QueryFaker(Expression expression, object results) : base(expression) { base.Provider = this; this._results = results; } public IQueryable CreateQuery(Expression expression) { return new QueryFaker(expression, _results); } public IQueryable CreateQuery(Expression expression) { return new QueryFaker(expression, _results); } public TResult Execute(Expression expression) { return (TResult)_results; } public object Execute(Expression expression) { return _results; } }

Excuse the formatting – copy and paste sucks today. Surprised smile

Comments (4)

  1. tilovell09 says:

    Hi Alex,

    Thanks for the link of interest. I've added my own suggestion there, to see how it fares with the votes, perhaps I'll eventually get some more data on if it's a good idea or not that way. 😀


  2. Alexandre Brisebois says:


    I've been down this path a couple times. In order to create testable Queries and Commands I use the specification pattern and wrap the code in Objects.

    These objects provide self documenting capabilities by having meaningful file names. Query and Command object can be reused and execute over multiple databases in parallel. This is great for fan out queries and for double entry systems.

    Having objects means that they are testable. Having the model (DbContext, object collection or other ) passed in as a parameter to the execution means that I can reuse the same query over multiple model instances.

    I also implemented query level caching for EF using these Query objects.

    Example of Query objects used with decoration for query level caching:…/query-level-caching-for-entity-framework-and-windows-azure-sql-database-including-transient-fault-handling



  3. tilovell09 says:

    Hey Alexandre, am enjoying reading about your LINQ with built-in caching! Thanks for a good read!

Skip to main content