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.

Specification pattern

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.

Serverless360 Logo

A portal focused on Operations and Support for Microsoft Azure Serverless services

FREE TRIAL

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    14 thoughts on “Implementing Query Specification pattern in Entity Framework Core

    • Pingback:Readable fluent queries with Entity Framework Core

    • February 12, 2020 at 9:38 am
      Permalink

      Hi,

      Thanks for this great post. I have a question for switch from EF to Drapper.

      In my services (in the layer of ApplicationCore) i call the query speficiation (in the ApplicationCore), and i pass the QuerySpecification ad my repository (in the Infrastractor Layer).

      In theory I could change the Infrastractor Layer (maybe for use Drapper), without change my ApplicationCore, right?

      var querySpecification = new ProductWithParentsSpecification(productId)
      _productRepository.List(querySpecification ).FirstOrDefault();

      But how can drapper convert querySpecification?

    • February 12, 2020 at 9:44 am
      Permalink

      Dapper is micro ORM (https://gunnarpeipman.com/micro-orm/) and it doesn’t support many things out-of-box like full-blown ORM-s like EF Core and NHibernate. As Dapper doesn’t have official support for LINQ you have two options: build Dapper-specific query specifications or write translator that turns LINQ expression trees to SQL. I think writing query specifications will be easier to do.

    • February 19, 2020 at 10:18 am
      Permalink

      I’m interested. Very good example. But how to create ThenInclude? I can’t create with this scheme.

    • March 1, 2020 at 10:19 pm
      Permalink

      Hi,

      Do you prefer to use specification pattern for very basic queries like ‘q => q.Id == id’ ? I think, this pattern should be used for some complex and re-usable queries, what do you think?

    • March 2, 2020 at 10:41 am
      Permalink

      Hi,
      I think specification pattern is good for more complex and re-usable queries. I wouldn’t bother much for simple cases like find something by ID or find person by SSN.

    • March 30, 2020 at 12:55 pm
      Permalink

      Hi Gunnar!
      Many thanks for the article.
      Could You tell, how to implement “Include” method ( from QuerySpecificationExtensions :

      .Aggregate(query,
      (current, include) => current.Include(include));

      VS reports error on the last row and doesn’t sujjest any fixes.

      With respect, Roman

    • March 30, 2020 at 2:48 pm
      Permalink

      Hi!

      What error VS is giving you?

      I usually add includes right after specifying DbSet from what I want data.

    • April 1, 2020 at 10:36 am
      Permalink

      Gunnar, just fixed it myself – the “Include” method is from System.Data.DataSetExtensions.

      Could You explain on another point-
      i’ve found out, that if my datamodels doesn’t support joins by model design (i’m dealing with a given db and join is made not by keys, but on several conditions) – so i can’t use your solution for such cases with complex joins, am i right?

    • April 1, 2020 at 10:48 am
      Permalink

      I have to confess I have not tried out this scenario. Can you give me some example of your LINQ queries so I understand better what you have?

    • April 2, 2020 at 11:53 am
      Permalink

      Hello Gunnar, here it is:

      (from vo in dbContext.VoGOprionsDays
      join cd in dbContext.VoCommittingChangesDeals
      on new { Id = (int?)vo.Id, TableName = “VoGOprionsDay”}
      equals new { Id = cd.DealSer, cd.TableName } into lj
      from x in lj.DefaultEmpty()
      where vo.DealStatus == “New” || x.DealSer != null
      select new
      {
      vo.Id
      }).Count();

      also, i’ve not found any way to write such join using query linq syntax , not sql syntax

    • April 4, 2020 at 9:18 am
      Permalink

      Hi Roman,

      Thanks for sample. As I see it’s query style LINQ and there you can’t use query specifications without moving at some point to LINQ methods. Back in days when I learned LINQ I used LINQPad to translate queryes to methods: https://www.linqpad.net/Download.aspx Not sure what progress this tool has made meanwhile but maybe it can help you. There is Join() method in LINQ to join tables.

      If I find some time to make more experiments on your queries I will try to find out if it’s possible to get these to LINQ methods. AFAIK then all LINQ queries are translatable to LINQ methods.

    • May 23, 2020 at 11:10 am
      Permalink

      For ThenInclude, use an overload accepting a string parameter.
      It would look something like this:
      AddInclude(“ObjectA.ObjectB”);

    • September 3, 2020 at 12:15 am
      Permalink

      Thanks for the great post. I have one question. Let’s assume that you have to check if the customer has an open invoice for the same item. In addition, there is a phone number property for customer, and it needs to be updated. How would you go about doing the update in this case?

    Leave a Reply

    Your email address will not be published. Required fields are marked *