Supercharge Your Entity Framework Core Performance: 11 Essential Tips

Entity Framework Core (EF Core) is a powerful Object-Relational Mapper (ORM) that simplifies database interactions in .NET applications. However, like any tool, it’s important to use it efficiently to avoid performance pitfalls. This article outlines 11 essential techniques to optimize your EF Core code and boost application speed.

1. The Power of Projections:

Retrieving entire entities when you only need a few columns is a common performance bottleneck. Projections allow you to select only the necessary data, dramatically reducing data transfer and improving query speed. Instead of fetching all columns with _context.Orders.ToList(), use projections:

var orderDetails = await _context.Orders
    .Select(o => new { o.OrderId, o.OrderDate, o.Customer.Name }) // Anonymous object
    .ToListAsync();

public class OrderSummary { /* Properties for Order ID, Date, and Customer */ }
var orderSummaries = await _context.Orders
    .Select(o => new OrderSummary  { /* Populate DTO */ })
    .ToListAsync();        

2. Split Complex Queries:

EF Core can sometimes generate slow, complex SQL queries with large JOINs. In such cases, AsSplitQuery() can split these complex queries into multiple simpler ones, enhancing performance.

var blogs = await _context.Blogs
    .Include(b => b.Posts)
    .AsSplitQuery() // Split queries for blogs and posts
    .ToListAsync();        

Without AsSplitQuery(): EF Core would generate a single SQL query with a JOIN between the Blogs and Posts tables. This query would retrieve all the necessary data in one go. While this can be efficient in some cases, it can also lead to performance issues, especially with large collections of posts. The single query can become very complex and return a lot of redundant data (blog information repeated for each related post).

With AsSplitQuery(): EF Core generates two separate SQL queries:

• The first query retrieves all the Blog entities.
• The second query retrieves all the related Post entities for the retrieved blogs. EF Core then "stitches" these results together in memory, populating the Posts collection of each Blog object.

When to use AsSplitQuery(): It's often beneficial when dealing with large related collections. Splitting the query can reduce the complexity of the individual queries and prevent the database from returning a lot of redundant data. It can also be helpful if the JOINed query is timing out.

When not to use AsSplitQuery(): For smaller collections, the overhead of the extra round trip might outweigh the benefits. It's essential to profile and benchmark your code to determine the best approach for your specific scenario. If the related data is small, the single JOIN query might be more efficient.

The main difference is the number of database round trips. AsSplitQuery() results in two round trips, while the default behavior (without it) uses one.

3. Embrace Asynchronous Operations:

Always use asynchronous methods like ToListAsync(), SaveChangesAsync(), and FindAsync() to prevent blocking the main thread and keep your application responsive. This is a fundamental best practice for any I/O-bound operation.

var products = await _context.Products.ToListAsync();        

4. AsNoTracking() for Read-Only Scenarios:

If you're only reading data and don't plan to modify entities, using AsNoTracking() tells EF Core not to track changes. This reduces overhead and improves performance, especially for large datasets.

var products = await _context.Products.AsNoTracking().ToListAsync();        

5. Optimize Count() and Any():

Avoid retrieving entire collections when all you need is the count or existence of entities. Use CountAsync() to get the number of items and AnyAsync() to check if any entities match a condition, reducing unnecessary data loading.

int productCount = await _context.Products.CountAsync();
bool hasProducts = await _context.Products.AnyAsync(p => p.Price > 100);        

6. Minimize SaveChanges() Calls:

Batch your changes and call SaveChangesAsync() only once to minimize database round trips. This is crucial when inserting or updating multiple entities.

_context.Products.AddRange(newProductList); // Add multiple products
await _context.SaveChangesAsync(); // Save changes once        

7. IQueryable Reuse: A Balancing Act:

Reusing IQueryable can be beneficial, but be cautious. Ensure you apply all necessary filtering and transformations before materializing the query with methods like ToListAsync(). This prevents premature execution and allows EF Core to optimize the entire query.

IQueryable<Product> query = _context.Products.Where(p => p.Category == "Electronics");

if (userIsAdmin) {
    query = query.Where(p => p.Discontinued == false);
}

var products = await query.ToListAsync(); // Query executed after all filters        

8. Use Compiled Queries for Recurring Operations:

For frequently executed queries, compiled queries offer a significant performance boost. EF Core compiles the query into a reusable form, reducing the overhead of query translation each time its executed.

private static readonly Func<DbContext, int, Task<Product>> _getProductById =
    EF.CompileAsyncQuery((DbContext context, int id) =>
        context.Products.FindAsync(id));

var product = await _getProductById(_context, 123);        

9. Don't Overlook Indexes:

Indexes are vital for database performance. Ensure that frequently queried columns are indexed to speed up data retrieval. You can create and manage indexes with migrations.

10. Raw SQL: Use with Care:

LINQ might not be expressive enough for highly complex or optimized queries. Raw SQL allows you to write any SQL query, giving you full control over the structure and logic of the query.

With raw SQL, you can take advantage of advanced SQL features that may not be easily accessible through LINQ, such as window functions, common table expressions (CTEs), complex joins, or other database-specific features.

Since raw SQL allows direct interaction with the database, it can sometimes be more efficient than LINQ for complex queries. You can optimize the query at the SQL level, ensuring that the database engine can execute it as efficiently as possible.

However, use them sparingly and be extremely mindful of SQL injection vulnerabilities. Parameterize your queries to prevent this.

var products = await _context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
    .ToListAsync();        

11. Understand Query Translation: The Debugger's Friend:

EF Core provides robust logging capabilities, allowing you to inspect the SQL queries it generates. This helps you understand how your LINQ queries are translated into SQL and identify performance bottlenecks. Enable logging in your DbContext configuration:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer("your_connection_string")
        .LogTo(Console.WriteLine, LogLevel.Information); // Log to console
        //.EnableSensitiveDataLogging(); // Be cautious with production data
}        

By implementing these 11 techniques, you’ll significantly boost the performance of your EF Core-based applications, enhancing the user experience. Remember, profiling and benchmarking are key to identifying the optimizations that will have the greatest impact on your specific workload.