Idea for EF Core 7: Fetch aggregate entity instance and related tables in single database trip by combining AsSplitQuery and AsSQLBatch

[maulik-modi]

Problem statement

Considering the relational database schema, I am wondering best way to retrieve/model aggregate root and value objects with required FK efficiently by user defined Unique key(ClientId).

We have many real world requirements, I am here illustrating the case with famous Dundesoftware recommended by @blowdart in https://devblogs.microsoft.com/dotnet/asp-net-core-6-and-authentication-servers/ and maintained by @leastprivilege and @brockallen

Expected output from EF Core (Note there is no GO statement between queries)

select clients.* 
from clients
where  
clients.clientid = @myclientid 

select 
  vobject.* 
from 
  clientredirecturis vobject 
  inner join clients on vobject.id = clients.id 
where 
  clients.clientid = @myclientid 

select 
  vobject.* 
from 
  clientidprestrictions vobject 
  inner join clients on vobject.id = clients.id 
where 
  clients.clientid = @myclientid 

select 
  vobject.* 
from 
  AllowedGrantTypes vobject 
  inner join clients on vobject.id = clients.id 
where 
  clients.clientid = @myclientid

goes on for all tables

Scenario

• Client is aggregate root entity
• ClientCORS Origin, ClientClaims and all other tables have Mandatory FK relationship with Client entity
• Client table has 1...N relationship with child tables
• Some tables are optional e.g. ClientPostLogoutRedirecturi
• ClientId is user defined Unique Key that is used to fetch value objects for that client id when client tries to login
• ClientCORS Origin and ClientClaims have composite unique key to avoid duplicates leading (prevent non-deterministic)

Constraints

1. Cannot generate Single query with LEFT JOIN as it will cause Matrix multiplication
2. Cannot generate Single query with INNER JOIN as some tables are optional
3. Cannot use AsSplitQuery as it requires 1database trip per relation and degrades performance, discussion thread is here -> EF Enhancement? DuendeSoftware/IdentityServer#298

Design goals

1. Generate multiple inner join query between aggregate root entity instance and dependent value objects similar to AsSplitQuery behavior

Two attempts to fix (Poorly performs in production)

https://github.com/DuendeSoftware/IdentityServer/blob/5.2.4/src/EntityFramework.Storage/Stores/ClientStore.cs#L53-L70
https://github.com/DuendeSoftware/IdentityServer/blob/releases/6.0.x/src/EntityFramework.Storage/Stores/ClientStore.cs

Performance related customer reported issues

DuendeSoftware/IdentityServer#694
DuendeSoftware/IdentityServer#668
https://github.com/DuendeSoftware/IdentityServer/discussions/667

It all started here DuendeSoftware/IdentityServer#298

Proposed solution (Need to satisfy design goals)

Use AsSplitQuery to generate Inner join style query between aggregate root and value object
Take advantage of ADO.NET Batch to run all Split queries in single database trip as initiated by @roji dotnet/runtime#15375, something like AsBatch() considering EF Core team has #18990 in EF Core 7 roadmap

[roji]

Duplicate of #10879

[roji]

Executing multiple (unrelated) LINQ queries in a single roundtrip (batch) is covered by #10879. Batching the split queries produced by EF Core for a single LINQ query is covered by #10878.