Monday, June 11, 2018

Entity Framework Core 2.1 - Avoiding the n+1 issue

With the release of Entity Framework Core 2.1, the team did some changes to the query translation to avoid executing n+1 SQL queries.

The n+1 problem occurs when you first fetch the some root data and later on use a navigation property while looping through all results(for example when you project your domain model to a viewmodel to return it from an API call).

An example; the following query normally gets translated into one query for Customers, plus N (where “N” is the number of customers returned) separate queries for Orders:

To avoid the n+1 issue, you can optimize this query by buffering the results from the sub-query. To achieve this, you have to modify your query and add a ToList() in the right place:

An example; this time the query will be translated to only two SQL queries: One for Customers and the next one for Orders.

More information: https://blogs.msdn.microsoft.com/dotnet/2018/05/30/announcing-entity-framework-core-2-1/

No comments: