Client retrieval creates a dreadful SQL query.

[DeadCatEdz]

Whilst investigating a timeout exception I was horrified to discover the SQL query that is produced when retrieving a single client.
The generated SQL is at the end.
It has series of LEFT joins on the Clients table for the child tables, with the left joins only being done by the client id
What this means is that the amount of records (for a singular client) is a cross product of the sizes of the child table.
For instance we have client which has the following counts for the children.
4 * 9 * 6 * 58 * 6 * 1 * 0 (->1) * 7 * 0 (->1) = 526176

i.e. it returns 0.5 million records for 1 client!

Such is the joy of Entity Framework.

Looking at the code, I could see a series of Include - I guess it was thought that these would separately include the data for that client id, rather doing it all in 1 statement?

---- SQL ----

SELECT [c9].[Id], [c9].[AbsoluteRefreshTokenLifetime], [c9].[AccessTokenLifetime], [c9].[AccessTokenType], [c9].[AllowAccessTokensViaBrowser], [c9].[AllowOfflineAccess], [c9].[AllowPlainTextPkce], [c9].[AllowRememberConsent], [c9].[AllowedIdentityTokenSigningAlgorithms], [c9].[AlwaysIncludeUserClaimsInIdToken], [c9].[AlwaysSendClientClaims], [c9].[AuthorizationCodeLifetime], [c9].[BackChannelLogoutSessionRequired], [c9].[BackChannelLogoutUri], [c9].[CibaLifetime], [c9].[ClientClaimsPrefix], [c9].[ClientId], [c9].[ClientName], [c9].[ClientUri], [c9].[ConsentLifetime], [c9].[CoordinateLifetimeWithUserSession], [c9].[Created], [c9].[DPoPClockSkew], [c9].[DPoPValidationMode], [c9].[Description], [c9].[DeviceCodeLifetime], [c9].[EnableLocalLogin], [c9].[Enabled], [c9].[FrontChannelLogoutSessionRequired], [c9].[FrontChannelLogoutUri], [c9].[IdentityTokenLifetime], [c9].[IncludeJwtId], [c9].[InitiateLoginUri], [c9].[LastAccessed], [c9].[LogoUri], [c9].[NonEditable], [c9].[PairWiseSubjectSalt], [c9].[PollingInterval], [c9].[ProtocolType], [c9].[PushedAuthorizationLifetime], [c9].[RefreshTokenExpiration], [c9].[RefreshTokenUsage], [c9].[RequireClientSecret], [c9].[RequireConsent], [c9].[RequireDPoP], [c9].[RequirePkce], [c9].[RequirePushedAuthorization], [c9].[RequireRequestObject], [c9].[SlidingRefreshTokenLifetime], [c9].[UpdateAccessTokenClaimsOnRefresh], [c9].[Updated], [c9].[UserCodeType], [c9].[UserSsoLifetime], [c0].[Id], [c0].[ClientId], [c0].[GrantType], [c1].[Id], [c1].[ClientId], [c1].[RedirectUri], [c2].[Id], [c2].[ClientId], [c2].[PostLogoutRedirectUri], [c3].[Id], [c3].[ClientId], [c3].[Scope], [c4].[Id], [c4].[ClientId], [c4].[Created], [c4].[Description], [c4].[Expiration], [c4].[Type], [c4].[Value], [c5].[Id], [c5].[ClientId], [c5].[Type], [c5].[Value], [c6].[Id], [c6].[ClientId], [c6].[Provider], [c7].[Id], [c7].[ClientId], [c7].[Origin], [c8].[Id], [c8].[ClientId], [c8].[Key], [c8].[Value]
FROM (
SELECT TOP(2) [c].[Id], [c].[AbsoluteRefreshTokenLifetime], [c].[AccessTokenLifetime], [c].[AccessTokenType], [c].[AllowAccessTokensViaBrowser], [c].[AllowOfflineAccess], [c].[AllowPlainTextPkce], [c].[AllowRememberConsent], [c].[AllowedIdentityTokenSigningAlgorithms], [c].[AlwaysIncludeUserClaimsInIdToken], [c].[AlwaysSendClientClaims], [c].[AuthorizationCodeLifetime], [c].[BackChannelLogoutSessionRequired], [c].[BackChannelLogoutUri], [c].[CibaLifetime], [c].[ClientClaimsPrefix], [c].[ClientId], [c].[ClientName], [c].[ClientUri], [c].[ConsentLifetime], [c].[CoordinateLifetimeWithUserSession], [c].[Created], [c].[DPoPClockSkew], [c].[DPoPValidationMode], [c].[Description], [c].[DeviceCodeLifetime], [c].[EnableLocalLogin], [c].[Enabled], [c].[FrontChannelLogoutSessionRequired], [c].[FrontChannelLogoutUri], [c].[IdentityTokenLifetime], [c].[IncludeJwtId], [c].[InitiateLoginUri], [c].[LastAccessed], [c].[LogoUri], [c].[NonEditable], [c].[PairWiseSubjectSalt], [c].[PollingInterval], [c].[ProtocolType], [c].[PushedAuthorizationLifetime], [c].[RefreshTokenExpiration], [c].[RefreshTokenUsage], [c].[RequireClientSecret], [c].[RequireConsent], [c].[RequireDPoP], [c].[RequirePkce], [c].[RequirePushedAuthorization], [c].[RequireRequestObject], [c].[SlidingRefreshTokenLifetime], [c].[UpdateAccessTokenClaimsOnRefresh], [c].[Updated], [c].[UserCodeType], [c].[UserSsoLifetime]
FROM [Clients] AS [c]
WHERE [c].[Id] = @__clientId_0
) AS [c9]
LEFT JOIN [ClientGrantTypes] AS [c0] ON [c9].[Id] = [c0].[ClientId]
LEFT JOIN [ClientRedirectUris] AS [c1] ON [c9].[Id] = [c1].[ClientId]
LEFT JOIN [ClientPostLogoutRedirectUris] AS [c2] ON [c9].[Id] = [c2].[ClientId]
LEFT JOIN [ClientScopes] AS [c3] ON [c9].[Id] = [c3].[ClientId]
LEFT JOIN [ClientSecrets] AS [c4] ON [c9].[Id] = [c4].[ClientId]
LEFT JOIN [ClientClaims] AS [c5] ON [c9].[Id] = [c5].[ClientId]
LEFT JOIN [ClientIdPRestrictions] AS [c6] ON [c9].[Id] = [c6].[ClientId]
LEFT JOIN [ClientCorsOrigins] AS [c7] ON [c9].[Id] = [c7].[ClientId]
LEFT JOIN [ClientProperties] AS [c8] ON [c9].[Id] = [c8].[ClientId]
ORDER BY [c9].[Id], [c0].[Id], [c1].[Id], [c2].[Id], [c3].[Id], [c4].[Id], [c5].[Id], [c6].[Id], [c7].[Id]

[skoruba]

What do you suggest? thanks

[DeadCatEdz]

Separate queries for the tables and then bring the data in at the end.
To be honest, I thought the EF include statement did do that, but it was couple of years ago when I was experimenting with it.

...darn, checking our API and the EF includes gives the same thing.

[DeadCatEdz]

Ha!
You need to add .AsSplitQuery()
https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries

e.g.

        private async Task<Client> GetDbClient(string clientId, CancellationToken ct = default)
        {
            return await DbContext.Clients.Where(c => c.ClientId == clientId)
                .Include("ClientSecrets")
                .Include("AllowedGrantTypes")
                .Include("RedirectUris")
                .Include("AllowedScopes")
                .Include("Properties")
                .Include("PostLogoutRedirectUris")
                .Include("IdentityProviderRestrictions")
                .Include("AllowedCorsOrigins")
                .Include("Claims")
                .AsSplitQuery()
                .SingleOrDefaultAsync(ct);
        }

or, it can be done systematically, e.g.

            services.AddDbContext<IdentityServerConfigurationDbContext>(options =>
                options.UseSqlServer(
                    authConnectionString,
                    o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
                )
            );

Which, then, is something I can do within my code and not require an update from yourself.

[skoruba]

Interesting, thanks for your tip! I will play with it.