EF Core Compiled Queries: Do They Really Make It Faster?

Compiled queries always come up among EF Core's performance tips. You compile a query once and reuse it, and EF Core doesn't have to translate LINQ to SQL every time. On paper it makes sense. But how much does it gain? I was curious, so I measured it with BenchmarkDotNet.

The short answer: it does help, but less than I expected. And how much it helps depends largely on how fast the database responds.

What does EF Core do when it runs a query?

A LINQ query is really an expression tree. To run it, EF Core goes through these steps:

1. It walks the expression tree and turns the variables in it into parameters.
2. It computes a cache key from the tree.
3. It looks in its cache for a query compiled with that key.
4. If there isn't one, it translates LINQ to SQL and generates the code that reads the results. It puts that in the cache.
5. It sends the SQL to the database and turns the results into objects.

The important point: EF Core already has its own cache. The second time you run the same query, step 4 is skipped. So a compiled query doesn't skip the SQL translation; it skips steps 1–3. There's no need to walk the expression tree and compute the cache key every time.

How do you write one?

private static readonly Func<ShopContext, int, Task<SalesOrder?>> OrderById =
    EF.CompileAsyncQuery((ShopContext db, int id) =>
        db.Orders.AsNoTracking().FirstOrDefault(o => o.Id == id));

// usage
var order = await OrderById(db, 250);

For queries that return several records, the result is an IAsyncEnumerable:

private static readonly Func<ShopContext, int, IAsyncEnumerable<SalesOrder>> RecentOrdersOfCustomer =
    EF.CompileAsyncQuery((ShopContext db, int customerId) =>
        db.Orders.AsNoTracking()
            .Include(o => o.Lines)
            .Where(o => o.CustomerId == customerId && o.Lines.Any(l => l.Quantity > 1))
            .OrderByDescending(o => o.CreatedAt)
            .Take(10));

await foreach (var order in RecentOrdersOfCustomer(db, 7))
{
    // ...
}

Notice that inside the compiled query I wrote FirstOrDefault, not FirstOrDefaultAsync. The query itself is asynchronous; the LINQ operators inside it are written synchronously.

I measured it

I prepared two queries:

• Simple: fetch a single order by Id.
• Complex: a customer's last 10 orders that have at least one line with more than one item, together with their lines.

I wrote both with plain LINQ and as compiled queries. Each measurement creates a new DbContext, as a real application does per request. I measured on two databases: PostgreSQL 16 in Docker on the same machine, and SQLite in memory. I added SQLite to bring the network and database cost close to zero, so EF Core's own cost shows more clearly.

Environment: Intel Core i7-13700H, 32 GB RAM, .NET 10, EF Core 10, BenchmarkDotNet 0.15.8.

PostgreSQL

SQLite (in memory)

What do the results say?

A compiled query saves roughly the same amount of time every time. About 30–40 µs for the simple query and 40–100 µs for the complex one. That's EF Core's cost of walking the expression tree and looking in its cache.

But the share of that saving depends on the database. SQLite runs in memory, so the total time is short and the 30 µs saved is a big share: 26%. On PostgreSQL the round trip alone exceeds 500 µs, and the same saving drops to 7%. If the production database is on another server, each round trip adds at least a few hundred microseconds more and the share gets even smaller.

The memory saving is more consistent. On both databases allocated memory dropped by 11–15%. For a query that runs very often, that eases the load on the garbage collector.

When should you use them?

I don't use compiled queries for every query. They make the code harder to read and you can't change the query at run time: things like adding a Where based on a condition aren't possible.

Here's where I use them: queries that run many times per second, never change shape and return quickly. For example a user or settings query that runs on every request, or the query behind the most-called endpoint of an API. There, a few tens of microseconds multiplied by thousands of requests per second turns into meaningful CPU time.

If you want to speed up a slow page, there are other places to look before compiled queries. In the N+1 problem I took 147 queries down to 1 and 114 ms down to 1.4 ms. In EF Core Query Performance I covered projection, AsNoTracking and AsSplitQuery. Those save milliseconds; compiled queries save microseconds.

A few things to know

• Keep the compiled query in a static readonly field. If you call EF.CompileAsyncQuery every time, you gain nothing.
• The number of parameters is limited. Besides the context, EF.CompileAsyncQuery accepts at most 15 parameters. If you need more, it's time to rethink the query.
• The "precompiled queries" that came with EF Core 9 are something different. That's an experimental feature that generates queries at build time so they can run under Native AOT. The compiled queries in this article are compiled at run time, on the first call.

Conclusion

Compiled queries really do make things faster: between 3% and 26% in my measurements. But the gain is a fixed amount, not a ratio. The slower the database responds, the less you feel it. So I look at the number of queries and the data being fetched first. I use compiled queries last, for the queries that run most often.