Improving EF Core performance with Compiled Queries

EF Core 2.0 is production ready and it's now fully RTM. Therefore, I hope that by now you have had the chance to take it for a spin and use it in your code. There are many compelling reasons why you would want to use EF Core such as performance, portability and size being. The latest release of 2.0 has added a number of new features making it a very compelling ORM.

Friendly reminder: EF Core can run both on the full .NET Framework and .NET Core. Don't let the "core" in the name confuse you.

Today I will show you one of these cool features that allows you to improve your query performance by using explicitly compiled queries. It's important to clarify that EF Core already automatically compiles and caches your queries using a hashed representation of the query expression. When your code needs to reuse a previously executed query, EF Core uses the hash to lookup and return the compiled query from the cache.

However, you may want to bypass the computation of the hash and the cache lookup using compiled queries directly. This can be achieved by using the newly exposed extension method in the EF static class:

• EF.CompileQuery()
• EF.CompileAsyncQuery()

These methods allow you to define a compiled query and subsequently call it through the invocation of a delegate.

I'm pretty excited about this feature so I decided to run a quick test to determine whether I could see any tangible performance improvements, even on a small test DB.

So? Was there a performance improvement? A picture's worth a 1000 words and number talk, so....

Below, I've attached the code that runs the same query twice, once relying on the built it EF Core cache and once with an explicitly compiled query

The important bit is this:

var query = EF.CompileQuery((BloggingContext db, int id)
    => db.Blogs.Single(c => c.BlogId == id));

using (var db = new BloggingContext())
{
    foreach (var id in blogIds)
    {
        var customer = query(db, id)
    }
}

The first part defines the compiled query while the second bit makes use of the query object by passing the DbContext and the expected query parameter.

EDIT: after posting this, I had a few questions asking whether the performance gains were because SQL Server would cache the queries and hence give false positives. It's important to remember that when working with EF, there are 2 types of caching:

1. EF caching LINQ to SQL queries
2. SQL Server caching SQL queries

Compiled queries target the 1st type, hence reducing the time to find the necessary query to execute against SQL.

Just for completion, I decided to run the test multiple times and even changing the order that the commands were executed, i.e compiled vs regular and I attached the run results below:

This is one of the many new features introduced with the release of EF Core 2.0. Look out for follow ups on EF Core around:

• EF Core commands with string interpolation
• Querying with EF.Functions.Like
• DbContext Pooling
• Query Filters

As always, feel free to use the comments if you have any questions or feedback.