EF Core - Query splitting

In EF Core when fetching multiple entities in one request it can result in a cartesian product as the same data is loaded multiple times which negatively impacts performance.

An example is when I try to load a list of Customers with their ordered products:

	var customers = database
	.Customers
	.Include(m => m.Orders)
	.ThenInclude(c => c.Products);

view raw Customers.cs hosted with ❤ by GitHub

The resulting query causes a cartesian product as the customer data is duplicated for every ordered product in the result set. EF Core will generate a warning in this case as it detects that the query will load multiple collections.

I talked before about the AsSplitQuery method to solve this. It allows Entity Framework to split each related entity into a query on each table:

	var customers = database
	.Customers
	.AsSplitQuery()
	.Include(m => m.Orders)
	.ThenInclude(c => c.Products);

view raw CustomersSplitted.cs hosted with ❤ by GitHub

However you can also enable query splitting globally in EF Core and use it as the default. Therefore update your DbContext configuration:

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
	optionsBuilder
	.UseSqlServer(
	@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;ConnectRetryCount=0",
	o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
	}

view raw DbContext.cs hosted with ❤ by GitHub

More information

EF Core–AsSplitQuery() (bartwullems.blogspot.com)

Single vs. Split Queries - EF Core | Microsoft Learn