Improve EF Core Performance with Compiled Queries

The background

Compiled queries in .NET emerged as a response to the performance challenges faced in data retrieval operations, particularly in applications using ORMs like Entity Framework. Before the introduction of compiled queries, every data retrieval operation required the ORM to translate LINQ queries into SQL queries, a process that was both time-consuming and resource-intensive. This translation had to be done every time a query was executed, significantly impacting performance, especially in applications with high database interaction. Let's dive in...

How To Create Compiled Queries

Let's say we have a Domain class that represents a data model: How can we construct a basic query to retrieve a User using its Id? Very simple... Now, how do we convert this query into a Complied Query?

What we have done here? • GetUser field: This is a static field of type Func< AppDbContext, int, User>. It's a compiled query using Entity Framework's EF.CompileQuery method. • GetUser Method: his is a public instance method that takes an int id as a parameter. It uses the static GetUser field (the compiled query) to retrieve a User object from the database. It does so by passing this (the current instance of AppDbContext) and the provided id to the compiled query. And now we can just call the method created within the our context:

Why to use Compiled Queries?

Improved Performance

The primary advantage is faster execution time, as the query translation from LINQ to SQL is done only once and reused, which is particularly beneficial for queries executed frequently.

Efficient Resource Utilization

Since the query is compiled once and cached, it reduces CPU usage and resource consumption associated with the query compilation process.

Automatic Caching

EF Core automatically compiles and caches the most common queries, reducing the need for manual intervention and simplifying development.

When Not to use Compiled Queries?

Highly Dynamic Queries

Avoid them if your queries change frequently or are dynamically constructed based on various conditions.

Infrequently Executed Queries

Not necessary for queries executed rarely, as the performance benefit may be negligible.

Limited Resource Environments

If memory usage is a concern, be cautious with the number of compiled queries.

For additional EF Core performance strategies, see 4 EF Core Tips to Improve Performance and Pre-Optimized EF Core Query Techniques.

Wrapping Up

Compiled queries in EF Core are a powerful feature for optimizing database access in specific scenarios. They are most beneficial for improving performance in scenarios with repetitive and frequent database queries. However, their use should be balanced against the potential drawbacks, such as increased memory usage and complexity, especially in applications with dynamic query patterns or limited resources. That's all from me for today.

dream BIG!