Clients.Select(z => z.ToModel()) times out

[rwb196884]

Clients.Select(z => z.ToModel())

writes the query

SELECT [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], [c0].[Id], [c0].[ClientId], [c0].[Created], [c0].[Description], [c0].[Expiration], [c0].[Type], [c0].[Value], [c1].[Id], [c1].[ClientId], [c1].[GrantType], [c2].[Id], [c2].[ClientId], [c2].[RedirectUri], [c3].[Id], [c3].[ClientId], [c3].[PostLogoutRedirectUri], [c4].[Id], [c4].[ClientId], [c4].[Scope], [c5].[Id], [c5].[ClientId], [c5].[Provider], [c6].[Id], [c6].[ClientId], [c6].[Type], [c6].[Value], [c7].[Id], [c7].[ClientId], [c7].[Origin], [c8].[Id], [c8].[ClientId], [c8].[Key], [c8].[Value]
FROM [Clients] AS [c]
LEFT JOIN [ClientSecrets] AS [c0] ON [c].[Id] = [c0].[ClientId]
LEFT JOIN [ClientGrantTypes] AS [c1] ON [c].[Id] = [c1].[ClientId]
LEFT JOIN [ClientRedirectUris] AS [c2] ON [c].[Id] = [c2].[ClientId]
LEFT JOIN [ClientPostLogoutRedirectUris] AS [c3] ON [c].[Id] = [c3].[ClientId]
LEFT JOIN [ClientScopes] AS [c4] ON [c].[Id] = [c4].[ClientId]
LEFT JOIN [ClientIdPRestrictions] AS [c5] ON [c].[Id] = [c5].[ClientId]
LEFT JOIN [ClientClaims] AS [c6] ON [c].[Id] = [c6].[ClientId]
LEFT JOIN [ClientCorsOrigins] AS [c7] ON [c].[Id] = [c7].[ClientId]
LEFT JOIN [ClientProperties] AS [c8] ON [c].[Id] = [c8].[ClientId]
ORDER BY [c].[Id], [c0].[Id], [c1].[Id], [c2].[Id], [c3].[Id], [c4].[Id], [c5].[Id], [c6].[Id], [c7].[Id]

which with even a small amount of data (6 clients) can produce tens of thousands of rows -- and the query then times out while doing the sorting.

[RolandGuijt]

Can you please check the joined tables. Do you have a large number of rows in (one of) them? And if so are these necessary?

[rwb196884]

I've got it down by removing some CORS origins and redirect URLs, but it's really not great that this thing can explode with really very few records.

[RolandGuijt]

We had performance issues with this query before in the ClientStore implementation. Most problems were solved by switching to split queries in EF avoiding a cartesian explosion resulting in the many rows you're seeing.

Can you please try that and see if that works for you?

Clients.Select(z => z.ToModel()).AsSplitQuery())

If this query is used in a custom ClientStore you can alternatively or in addition also enable caching.

[rwb196884]

Ah, that sounds like what I need; didn't know about that. Thank you.