3 Ways to Optimize EF Core Queries

Entity Framework Core (EF Core) is an object-relational mapping (ORM) framework that allows you to manipulate relational data using .NET objects. EF Core provides a built-in query syntax based on Language Integrated Query (LINQ), which allows you to write queries in C# or VB.NET in a declarative and expressive way.

However, LINQ queries are not directly executed by the database, but are translated into SQL by EF Core. This translation can impact query performance, depending on the complexity of the LINQ code, the entity model, the database provider, and configuration settings.

Fortunately, EF Core offers several extension methods that allow you to optimize LINQ queries, by changing the way they are translated or executed. In this article, we will introduce some of these methods and see how to use them with real-world examples.

AsNoTracking

The method AsNoTrackingtells EF Core not to track the entities returned by the query. Tracking entities involves caching them in the context of EF Core and detecting changes to them. This mechanism is useful for managing the lifecycle of entities and for applying changes to the database when calling SaveChanges.

However, entity tracking comes at a cost in terms of memory and execution time. If you don’t need to modify the entities returned by the query, you can use the method AsNoTrackingto disable tracking and improve performance.

For example, if you just want to display the list of products in a category, you can write:

var products = context.Products
    .Where(p => p.CategoryId == categoryId)
    .AsNoTracking()
    .ToList();

The method AsNoTrackingcan be applied to any LINQ query that returns entities. It can also be configured globally for all queries in the context, using the ChangeTracker.QueryTrackingBehavior.

Note : In case of projected entities (with a `Select`), the returned objects are not tracked unless a navigation property is returned as is. In the example below the objects in the property p.OrderDetailswill be tracked.

var products = context.Products
 .Where(p => p.CategoryId == categoryId)
 .Select(p => new { p.Name, p.OrderDetails })
 .ToList();

AsSplitQuery

The method AsSplitQuerytells EF Core to split the LINQ query into multiple SQL queries, to reduce the number of columns and rows returned. This method is useful when the LINQ query includes either eager loading or explicit loading of navigation collections, that is, properties that reference other entities.

By default, EF Core uses a loading strategy called join loading, which involves joining the tables of related entities into a single SQL query. This strategy can lead to performance issues, such as data duplication, result bloating, and the use of complex joins.

The method AsSplitQueryavoids these problems by generating a separate SQL query for each loaded navigation collection. This way, the data is returned in a more efficient and readable way.

For example, if you want to load a customer’s orders and the details of each order, you can write:

var orders = context.Orders
    .Where(o => o.CustomerId == customerId)
    .Include(o => o.OrderDetails)
    .AsSplitQuery()
    .ToList();

This LINQ query will be translated into two SQL queries: one for orders and one for order details. The results will then be combined by EF Core in memory.

The method AsSplitQuerycan be applied to any LINQ query that uses the method Includeor ThenIncludeto load navigation collections. It can also be configured globally for all queries in the context, using the OptionsBuilder.UseQuerySplittingBehavior.

AsSingleQuery

The method AsSingleQuerytells EF Core to use the join loading strategy for the LINQ query. This method is the opposite of the method AsSplitQuery, and is useful when the default loading strategy has been changed globally.

Indeed, it may happen that the join loading strategy performs better than the split loading strategy, depending on the number and size of the related entities, the type of join used, and the support of the database vendor.

The method AsSingleQuerytherefore allows to force the join loading strategy for a specific LINQ query, by generating a single SQL query with joins.

For example, if you want to load products from a category and customer reviews on those products, you can write:

var products = context.Products
    .Where(p => p.CategoryId == categoryId)
    .Include(p => p.Reviews)
    .AsSingleQuery()
    .ToList();

This LINQ query will be translated into a single SQL query with a join between the products and reviews tables. The results will then be formatted by EF Core in memory.

The method AsSingleQuerycan be applied to any LINQ query that uses the method Includeor ThenIncludeto load navigation collections. It can also be configured globally for all queries in the context, using the OptionsBuilder.UseQuerySplittingBehavior.

Conclusion

AsNoTrackingIn this article, we saw how to optimize LINQ queries with EF Core, using the , AsSplitQueryand extension methods AsSingleQuery. These methods allow you to modify the way LINQ queries are translated or executed by EF Core, in order to improve performance and readability of the results.

There are other extension methods that can be useful for optimizing LINQ queries with EF Core, such as AsEnumerable, AsAsyncEnumerable, AsQueryable, which will be detailed in future articles!