Relational split query mode for collection includes

[smitpatel]

Design meeting decisions from first iteration of split queries

• In first iteration we will do split queries for basic include scenarios. e.g. context.Customers.Include(c => c.Orders)
  • Multiple query roots composed may work in simple scenarios.
  • Filtered include (new in 5.0), would work with Where/OrderBy naturally.
  • Filtered include with Skip/Take won't work at least in early iterations.
• EF Core sends separate DbCommands for each split query. Each DbCommand's DbReader will be buffered in BufferedDataReader, since we need data from all DbReaders to generate first result. EF Core will open multiple datareaders one for each query.
  • For Async queries we will reader from DataReader async way into BufferedDataReader
  • From BufferedDataReader we will always read sync way to materialize result
• There will be quaryable operator - AsSplitQuery which will mark the query to use split query methods.

Once above is released in preview, we will look for feedback on what to expand further.
Actual work item for #18022. Please use #18022 for discussions or any feedback.

[smitpatel]

Pending items

• Collections which are not wrapped in MaterializeCollectionNavigation (filed Split query for non-navigation collections #21234)
• Filtered includes with OrderBy

[smitpatel]

Filed #21276 for filtered include since it won't be in preview6.
Marking this issue as closed fixed in preview6.

[smitpatel]

In EF Core preview6, we are introducing a new API which is available for relational providers - AsSplitQuery
The API works on any EFCore queryable and can be placed anywhere, just like AsTracking/AsNoTracking.
Using the API will indicate EF that it should use multiple queries rather than single query to get results for collection includes and certain projection. It splits the query in similar way how EF Core 2.x did (albeit generated SQL for collection loading is bit different to allow translating even more operations).
Current support:

Case 1: Includes:
Collection includes with Filtered includes with predicates are working.
Query like context.Customers.Include(c => c.Orders).AsSplitQuery().ToList() generates following SQLs

SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
FROM [Customers] AS [c]
ORDER BY [c].[CustomerID]

SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate], [c].[CustomerID]
FROM [Customers] AS [c]
INNER JOIN [Orders] AS [o] ON [c].[CustomerID] = [o].[CustomerID]
ORDER BY [c].[CustomerID]

• The support allows almost all operations on query root where collection can be fetched using split queries. This includes OrderBy/Skip/Take, Join operations, FirstOrDefault and similar single result selecting operations.
• Filtered includes with OrderBy/Skip/Take are not presently working correctly and it can give incorrect results. Split query for filtered includes #21276 has been tracking it. It will be included in preview7 release.

Case 2: Collection projection:
Query likes context.Customers.Select(c => new { c, c.Orders }).AsSplitQuery().ToList() will generate SQL same as above.

• Currently it only identifies when collection navigation is materialized. Hence any composition after c.Orders in above case won't use split query. Split query for non-navigation collections #21234 tracks support for that.

Differences from EFCore 2.x

• EF Core 2.x translated projection of collection (case 2 above) which are composed using Where/OrderBy/Select. We will add support for it based on feedback on Split query for non-navigation collections #21234. Please upvote that issue if you want support for it.
• The generated query for collection loading is different. Main reason for that was lack of support when collection has Skip/Take. The new implementation allows translating Skip/Take also (filtered include with Skip/Take is already supported).
• We will also generate a warning when EF Core performs multiple collection projection/include in single query scenario to guide user towards AsSplitQuery if that is useful for them. See Generate a warning for multiple collection Includes #19933