Compiled Queries in Entity Framework Core 2.0

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 a database context class with a method to return the category by id. This method actually performs an eager load of the category and, if needed, then we can modify the meaning of the eager loading of the 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. Builds a LINQ query to get the category with a specified id.
2. Compile the query.
3. Run the query.
4. Materialize the results.
5. Return the category if found or null.

Suppose we have a site with product categories. As most of the requests above also need to load the current category, then the query above is very popular on our site.

Compiling the Query in Advance

Leaving out all optimizations we can implement in the database and on the web application level (output caching, response caching), let’s see what we can do with this method. In the method level, we cannot optimize much of how the query is run and how the results are returned, but we can save something by using a compiled query. This is also known as an explicitly compiled query. We define the compiled query as Func, which 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 a query for loading categories by id built, and calls to the 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 the ApplicationDbContext class. There are no threading issues between requests as each call to the _getCategory func is using the instance of the database context we provide with the 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 the 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 it to the limits and write an 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 about as asynchronous as things can currently be.  FirstOrDefaultAsync(), ToListAsync(), and other asynchronous calls that Entity Framework provides are currently not supported by compiled queries.

Wrapping Up

Support for compiled queries is feature categorized under high-availability in Entity Framework 2.0 introduction. It helps us raise the performance of the application by compiling 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 the most popular queries in web applications. I hope that in the 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.