EF Core–AsSplitQuery()

Entity Framework makes it really easy to fetch related data. For example let’s get all the Customers with their ordered products.

This involves 3 entities: Customer –> Order –> Product.

To fetch this in one go in EF Core, you can use the following LINQ query:

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

view raw Customers.cs hosted with ❤ by GitHub

On the database this will end up in a query like this:

	SELECT "c"."Id", "p"."LastName", "p"."FirstName", "t"."Id", "t"."ProductId", "t"."OrderDate", "t"."CustomerId", "t"."Id0", "t"."Name0"
	FROM "Customers" AS "c"
	LEFT JOIN (
	SELECT "o"."Id", "o"."ProductId", "o"."OrderDate", "p"."Id" AS "Id0", "p"."Name" AS "Name0"
	FROM "Orders" AS "o"
	INNER JOIN "Products" AS "p" ON "o"."ProductId" = "p"."Id"
	) AS "t" ON "c"."Id" = "t"."CustomerId"

view raw Customers.sql hosted with ❤ by GitHub

The problem is that this results in a cartesian product as the customer data is duplicated for every ordered product in the result set.

A solution exists in EF Core through the AsSplitQuery method that 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

More information: https://docs.microsoft.com/en-us/ef/core/querying/single-split-queries