Implementing Query Specification pattern in Entity Framework Core

My previous blog post No need for repositories and unit of work with Entity Framework Core showed how Repository and Unit of Work patterns are already implemented in Entity Framework Code (and NHibernate) and how we can use IDataContext generalization to get ORM-s behind same interface. As I’m still trying to find out what’s the best place where to isolate queries without exposing IQueryable I started playing with Query Specification pattern.

Speficiation and Query Specification patterns

The roots of query specification pattern are in specification and composite specification patterns. Query specification is popular pattern in Domain-Driven Design (DDD). When searching for specification pattern this is the most popular diagram we can see.

It illustrates ISpecification interface that represents specification pattern and CompositeSpecification that follows composite specification pattern. We can build extensive set of specifications and if we spice it up with fluent methods then we probably discover soon that we are building our own implementation of LINQ.

If we read DDD writings about specifications – take a look at blog post Specification pattern: C# implementation by Vladimir Khorikov – we can see that writings get very soon into data topics after introduction. To see some code take a look at the blog post I mentioned before.

Query specification pattern works surprisingly well with Entity Framework Core too as you will see soon.

Test classes

I took some sample classes from a project I use to teach students. We have Invoice, Customer and InvoiceLine classes – simple ones but good enough to demonstrate how things work.

public class Invoice
{
    [Key]
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public DateTime DueDate { get; set; }
    public bool IsPaid { get; set; }
 
    [Required]
    public Customer Customer { get; set; }
 
    [Required]
    [StringLength(12)]
    public string InvoiceNo { get; set; }
 
    public IList<InvoiceLine> Lines { get; set; }
 
    public Invoice()
    {
        Lines = new List<InvoiceLine>();
    }
}
 
public class Customer
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
}
 
public class InvoiceLine
{
    [Key]
    public int Id { get; set; }
    public string LineItem { get; set; }
    public double Price { get; set; }
    public string Unit { get; set; }
    public double Amount { get; set; }
    public double Sum { get; set; }
 
    [Required]
    public Invoice Invoice { get; set; }
}

I will use these classes also in this blog post. Here are my database context with IDataContext interface.

public interface IDataContext
{
    DbSet<Invoice> Invoices { get; set; }
    DbSet<InvoiceLine> InvoiceLines { get; set; }
 
    void BeginTransaction();
    void Commit();
    void Rollback();
}
 
public class LasteDbContext : DbContext, IDataContext
{
    public LasteDbContext(DbContextOptions<LasteDbContext> options)
        : base(options)
    {
    }
 
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Invoice> Invoices { get; set; }
    public DbSet<InvoiceLine> InvoiceLines { get; set; }
    
    private IDbContextTransaction _transaction;
 
    public void BeginTransaction()
    { 
        _transaction = Database.BeginTransaction();
    }
 
    public void Commit()
    {
        try
        {
            SaveChanges();

            _transaction.Commit();
        }
        finally
        {
            _transaction.Dispose();
        }        
    }
 
    public void Rollback()
    { 
        _transaction.Rollback();
        _transaction.Dispose();
    }
}

Those who want to try out the code shown here can use my database context and entities for experimenting.

Implementing query specification

I found different implementations for query specification. Some of these where small and simple while others where real monsters. I started going with implementation given in Implement the infrastructure persistence layer with Entity Framework Core chapter of Entity Framework Core documentation. I modified BaseSpeficiation<T> a little bit to make it more comfortable to use.

public interface ISpecification<T>
{
    Expression<Func<T, bool>> Criteria { get; }
    List<Expression<Func<T, object>>> Includes { get; }
    List<string> IncludeStrings { get; }
}
 
public abstract class BaseSpecification<T> : ISpecification<T>
{
    public Expression<Func<T, bool>> Criteria { get; set; }
    public List<Expression<Func<T, object>>> Includes { get; } = new List<Expression<Func<T, object>>>();
    public List<string> IncludeStrings { get; } = new List<string>();
 
    protected virtual void AddInclude(Expression<Func<T, object>> includeExpression)
    {
        Includes.Add(includeExpression);
    }
 
    protected virtual void AddInclude(string includeString)
    {
        IncludeStrings.Add(includeString);
    }
}

Using BaseSpecification<T> we can write query classes or specifications and use these with database context to get data from database.

I will not use repository classes here as I’m still not sure how we should call classes that host querying methods. Instead I will use extension method to apply query specification to DbSet-s of my database context.

public static class QuerySpecificationExtensions
{
    public static IQueryable<T> Specify<T>(this IQueryable<T> query, ISpecification<T> spec) where T : class
    {
        // fetch a Queryable that includes all expression-based includes
        var queryableResultWithIncludes = spec.Includes
            .Aggregate(query,
                (current, include) => current.Include(include));
 
        // modify the IQueryable to include any string-based include statements
        var secondaryResult = spec.IncludeStrings
            .Aggregate(queryableResultWithIncludes,
                (current, include) => current.Include(include));
 
        // return the result of the query using the specification's criteria expression
        return secondaryResult.Where(spec.Criteria);
    }
}

Using Specify() method we can apply query specifications to all our IQueryables defined in database context.

Building query specifications

Let’s write now some query specifications to try out how things work. First implementation is about getting unpaid invoices with due date in past.

public class DueInvoicesSpecification : BaseSpecification<Invoice>
{
    public DueInvoicesSpecification()
    {
        Criteria = i => i.DueDate < DateTime.Now.Date &&
                        i.IsPaid == false;
 
        Includes.Add(i => i.Customer);
    }
}

As this specification will be called directly or through application services layer by some controller action that lists problematic invoices we need to include only Customer. Invoice lines are not needed in list views and that’s why we don’t include them. For a sake of simplicity I use the specification directly in controller action.

public IActionResult Index()
{
    var invoices = _dataContext.Invoices.Specify(new DueInvoicesSpecification())
                                        .ToList();
 
    return View(invoices);
}

Here’s the query that Entity Framework Core generates for SQLite database.

SELECT 
    "i"."Id", 
    "i"."CustomerId", 
    "i"."Date", 
    "i"."DueDate", 
    "i"."InvoiceNo", 
    "i"."IsPaid", 
    "i.Customer"."Id", 
    "i.Customer"."Name"
FROM 
    "Invoices" AS "i"
    LEFT JOIN "Customers" AS "i.Customer" 
        ON "i"."CustomerId" = "i.Customer"."Id"
WHERE 
    ("i"."DueDate" < rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime'), '0'), '.'), 'start of day'), '0'), '.')) 
    AND ("i"."IsPaid" = 0)

We can clearly see that customer data is included and also our unpaid invoice condition is present (alhtough it looks damn ugly).

Wiring query specifications

Now let’s try one trick. I will add another specification that will filter out unpaid invoices of given customer. Then let’s see if wiring up multiple specifications works.

public class CustomerInvoicesSpecification : BaseSpecification<Invoice>
{
    public CustomerInvoicesSpecification(int customerId)
    {
        Criteria = i => i.Customer.Id == customerId;
    }
}

If we show list of something then we probably need sorting too. So, let’s wire our new specification and order-by to invoices query.

public IActionResult Index()
{
    var invoices = _dataContext.Invoices.Specify(new DueInvoicesSpecification())
                                        .Specify(new CustomerInvoicesSpecification(100))
                                        .OrderBy(i => i.Customer.Name)
                                        .ToList();
 
    return View(invoices);
}

Value of customer ID for specification and @__customerId_0 parameter is 100. Here’s the query that Entity Framework Core generated.

SELECT 
    "i"."Id", 
    "i"."CustomerId", 
    "i"."Date", 
    "i"."DueDate", 
    "i"."InvoiceNo", 
    "i"."IsPaid", 
    "i.Customer"."Id", 
    "i.Customer"."Name"
FROM 
    "Invoices" AS "i"
    LEFT JOIN "Customers" AS "i.Customer" 
        ON "i"."CustomerId" = "i.Customer"."Id"
    WHERE 
        (("i"."DueDate" < rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime'), '0'), '.'), 'start of day'), '0'), '.')) 
        AND ("i"."IsPaid" = 0)) 
        AND ("i"."CustomerId" = @__customerId_0)
ORDER BY 
    "i.Customer"."Name"

As long as our specifications are simple enough and doesn’t mess up LINQ expression trees with something too complex we can also wire query specifications one after another.

Wrapping up

Database providers for Entity Framework Core use LINQ to query databases. Although we can hide querying to repositories or some query store classes it’s possible to use Query Specification pattern to let developers of other layers build up their queries like with Lego blocks. Is it good or bad – it depends on skills of developers. Still wiring of query specifications seems cool to me. When building some big solution it’s good idea to consider how far it is possible to go with query specifications before some other approach turns out to be more effective.