Implementing Query Specification pattern in Entity Framework Core
Tags: dotnetcore, efcore, visualstudio
Published: 2020-01-27 17:14:27

---
title: Implementing Query Specification pattern in Entity Framework Core
published: true
date: 2019-01-27 11:02:58 UTC
tags: dotnetcore,efcore,visual-studio
canonical_url: https://gunnarpeipman.com/ef-core-query-specification/
---

My previous blog post [No need for repositories and unit of work with Entity Framework Core](https://gunnarpeipman.com/ef-core-repository-unit-of-work/ "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](https://en.wikipedia.org/wiki/Specification_pattern "Specification pattern @ Wikipedia"). 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](https://static.gunnarpeipman.com/wp-content/uploads/2019/11/specification-pattern.png "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](https://enterprisecraftsmanship.com/posts/specification-pattern-c-implementation/ "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.

```csharp
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.

```csharp
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](https://docs.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/infrastructure-persistence-layer-implemenation-entity-framework-core "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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.

```sql
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.

```csharp
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.

```csharp
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.

```sql
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.

The post [Implementing Query Specification pattern in Entity Framework Core](https://gunnarpeipman.com/ef-core-query-specification/) appeared first on [Gunnar Peipman - Programming Blog](https://gunnarpeipman.com).