Compiled queries in Entity Framework Core

Entity Framework Core 2.0 introduces explicitly compiled queries. These are LINQ queries that are compiled in advance to be ready for execution as soon as application asks for data. This blog post demonstrates how compiled queries work and how to use them.

How queries are executed?

Suppose we have database context class with method to return category by id. This method actually performs eager load of category and if needed then we can modify the meaning of eager loading of category in one place.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
     public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
     {
     }

     public DbSet<Category> Categories { get; set; }

     // ...

     public IList<Category> GetCategory(Guid id)
     {
         return Categories.Include(c => c.Translations)
                          .ThenInclude(c => c.Language)
                          .Include(c => c.Parent)
                          .Where(c => c.Id == id)
                          .FirstOrDefault();
     }

     // ...
}

Going step by step this is what the method above does:

1. Build LINQ query to get category with sepcified id
2. Compile query
3. Run query
4. Materialize results
5. Return category if found or null

Suppose we have site with product categories and as most of requests need also loading of current category then the query above is very popular in our site.

Compiling query in advance

Leaving out all optimizations we can do in database and on web application level (output caching, response caching) let’s see what we can do with this method. In method level we cannot optimize much how query is run and results are returned but we can save something on using compiled query. It is also known as explicitly compiled query. We define compiled query as Func that we can call later.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    private static Func<ApplicationDbContext, Guid, IQueryable<Category>> _getCategory =
            EF.CompileQuery((ApplicationDbContext context, Guid id) =>
                context.Categories.Include(c => c.Translations)
                                  .ThenInclude(c => c.Language)
                                  .Include(c => c.Parent)
                                  .Where(c => c.Id == id)
                                  .FirstOrDefault());
 
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }
 
    public DbSet<Category> Categories { get; set; }
 
    // ...
 
    public Category GetCategory(Guid id)
    {
        return _getCategory(this, id);
    }
 
    // ...
}

Now we have query for loading categories by id built once and calls to GetCategory() method don’t have to compile the query again. Notice that query compiling happens in static scope meaning that once built the query is used by all instances of ApplicationDbContext class. There are no threading issues between requests as each call to _getCategory func is using the instance of database context we provide with method call.

NB! Current version of Entity Framework Core (2.0.1) doesn’t support returning arrays, lists etc from explicitly compiled queries. Also IEnumerable<T> and IQueryable<T> are not working yet and throw exceptions when Func is called.

Asynchronous compiled queries

To make server use processors more effectively we can run Entity Framework queries asynchronously. It doesn’t make our application faster. Actually it adds one millimeter of overhead but we will win in better throughput. But let’s push to the limits and write asynchronous counterpart for our category query.

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    private static Func<ApplicationDbContext, Guid, Task<Category>> _getCategory =
            EF.CompileAsyncQuery((ApplicationDbContext context, Guid id) =>
                context.Categories.Include(c => c.Translations)
                        .ThenInclude(c => c.Language)
                        .Include(c => c.Parent)
                        .Where(c => c.Id == id)
                        .FirstOrDefault());
 
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }
 
    public DbSet<Category> Categories { get; set; }
 
    // ...
 
    public async Task<Category> GetCategoryAsync(Guid id)
    {
        return await _getCategory(this, id);
    }
 
    // ...
}

This is how asynchronous as things can currently be. FirstOrDefaultAsync(), ToListAsync() and other asynchronous calls that Entity Framework provides are currently not supported in compiled queries.

Wrapping up

Support for compiled queries is feature categorized under high-availability in Entity Framework 2.0 introduction. It helps us raise performance of application by ćompiling queries once and using these compiled queries later when actual calls for data are made. There are some limitations and not everything is possible yet but it’s still time to start investigating this feature and trying to apply it to most popular queries in web applications. I hope that in near future we will see also support for LINQ methods that return multiple results. My next post about compiled queries will focus on performance to show how much we can benefit from this feature.