In Entity Framework Core (EF Core), queries play a crucial role in retrieving data from a database. To optimize query performance, EF Core provides a feature called “compiled queries.” This feature allows developers to pre-compile LINQ queries into efficient query execution plans, resulting in improved performance and reduced overhead. In this blog post, we will explore EF Core’s compiled queries feature and demonstrate its usage with examples.
What are Compiled Queries?
Compiled queries in EF Core refer to LINQ queries that are pre-compiled into executable SQL statements. Unlike normal LINQ queries, which are dynamically compiled at runtime, compiled queries are transformed into parameterized SQL queries during the initial compilation phase. This transformation process allows EF Core to cache the compiled query execution plan, leading to significant performance improvements when executing the same query multiple times.
Benefits of Compiled Queries
Utilizing compiled queries in your EF Core applications can yield several benefits, including:
- Improved Performance: By eliminating the need for dynamic query compilation, compiled queries can significantly reduce the overhead associated with query execution, resulting in faster data retrieval.
- Reduced Database Round-Trips: The use of compiled queries enables EF Core to cache the query execution plan, reducing the number of database round-trips required for query execution, which can further enhance performance.
- Parameterization: Compiled queries support parameterization, allowing you to easily pass parameters to your queries. This helps prevent SQL injection vulnerabilities and promotes code reusability.
Using Compiled Queries in EF Core
To demonstrate the usage of compiled queries in EF Core, let’s consider a scenario where we have an AppDbContext
class representing the application's database context. We'll create a compiled query that retrieves a list of products based on a specific category:
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Linq.Expressions;
public class AppDbContext : DbContext
{
public DbSet<Product> Products { get; set; }
// Compiled query definition
private static readonly Func<AppDbContext, string, IQueryable<Product>> GetProductsByCategoryQuery =
EF.CompileQuery((AppDbContext context, string category) =>
context.Products.Where(p => p.Category == category));
// Usage of compiled query
public IQueryable<Product> GetProductsByCategory(string category)
{
return GetProductsByCategoryQuery(this, category);
}
}
In the code snippet above, we define a compiled query using the EF.CompileQuery
method. The compiled query takes an AppDbContext
instance and a string
parameter representing the category of the products to retrieve. The query selects all products from the Products
DbSet that match the specified category. Note that the compiled query is defined as a Func<AppDbContext, string, IQueryable<Product>>
.
To use the compiled query, we create a public method GetProductsByCategory
in the AppDbContext
class. This method accepts the category as a parameter and invokes the pre-compiled query, passing the current instance of the database context and the provided category. The compiled query is then executed and returns the filtered list of products.
Async Compiled Query
In addition to regular compiled queries, EF Core also supports async compiled queries. Async compiled queries enable developers to perform asynchronous database operations efficiently by combining the benefits of compiled queries and asynchronous programming. Let’s dive into how to use async compiled queries in EF Core.
To demonstrate the usage of async compiled queries, we will modify our previous example of retrieving products by category in the AppDbContext
class.
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
public class AppDbContext : DbContext
{
public DbSet<Product> Products { get; set; }
// Compiled query definition
private static readonly Func<AppDbContext, string, Task<IQueryable<Product>>> GetProductsByCategoryQuery =
EF.CompileAsyncQuery((AppDbContext context, string category) =>
context.Products.Where(p => p.Category == category));
// Usage of compiled query
public async Task<IQueryable<Product>> GetProductsByCategoryAsync(string category)
{
return await GetProductsByCategoryQuery(this, category);
}
}
In the modified code snippet, we introduced the EF.CompileAsyncQuery
method to create an async compiled query. This method allows us to compile a LINQ query into an asynchronous execution plan. The compiled query now returns a Task<IQueryable<Product>>
instead of a synchronous IQueryable<Product>
.
We also updated the GetProductsByCategoryAsync
method to be async. This method awaits the execution of the async compiled query and returns the resulting filtered products.