Reduce database queries for Identity UserClaimsPrincipalFactory and IRoleClaimStore

[JeffBarnard]

Summary

The default Identity RoleStore implements IRoleClaimStore, which adds support for RoleClaims (RoleManager.SupportsRoleClaims). There is no way to opt out of using role claims, short of one rolling their own RoleStore. The bigger concern is that the database performance is poor because it executes many round-trips when attempting to find role claims for the user.

This occurs when we use the SignInManager to signin or to CreateUserPrincipal.

src/UserClaimsPrincipalFactory L149

Motivation and goals

We noticed this pattern repeated while profiling our database after implementing an identity server. This set of queries against AspNetRoles and AspNetRoleClaims occur twice per sign in cycle, thus the number of stored proc calls is 4x the number of roles the user has. This can easily multiply into the hundreds per sign in request. When we factor in round-trip webservice->database latency, the wait times to authenticate a user can become lengthy.

63	rpc_completed	exec sp_executesql N'SELECT [a].[Id], [a].[ClaimType], [a].[ClaimValue], [a].[UserId]  FROM [AspNetUserClaims] AS [a]  WHERE [a].[UserId] = @__user_Id_0'
67	rpc_completed	exec sp_executesql N'SELECT [a0].[Name]  FROM [AspNetUserRoles] AS [a]  INNER JOIN [AspNetRoles] AS [a0] ON [a].[RoleId] = [a0].[Id]  WHERE [a].[UserId] = @__userId_0'
72	rpc_completed	exec sp_executesql N'SELECT TOP(1) [a].[Id], [a].[ConcurrencyStamp], [a].[Description], [a].[Name], [a].[NormalizedName]  FROM [AspNetRoles] AS [a]  WHERE [a].[NormalizedName] = @__normalizedName_0'
77	rpc_completed	exec sp_executesql N'SELECT [a].[ClaimType], [a].[ClaimValue]  FROM [AspNetRoleClaims] AS [a]  WHERE [a].[RoleId] = @__role_Id_0'
81	rpc_completed	exec sp_executesql N'SELECT TOP(1) [a].[Id], [a].[ConcurrencyStamp], [a].[Description], [a].[Name], [a].[NormalizedName]  FROM [AspNetRoles] AS [a]  WHERE [a].[NormalizedName] = @__normalizedName_0'
85	rpc_completed	exec sp_executesql N'SELECT [a].[ClaimType], [a].[ClaimValue]  FROM [AspNetRoleClaims] AS [a]  WHERE [a].[RoleId] = @__role_Id_0'
89	rpc_completed	exec sp_executesql N'SELECT TOP(1) [a].[Id], [a].[ConcurrencyStamp], [a].[Description], [a].[Name], [a].[NormalizedName]  FROM [AspNetRoles] AS [a]  WHERE [a].[NormalizedName] = @__normalizedName_0'
93	rpc_completed	exec sp_executesql N'SELECT [a].[ClaimType], [a].[ClaimValue]  FROM [AspNetRoleClaims] AS [a]  WHERE [a].[RoleId] = @__role_Id_0'
97	rpc_completed	exec sp_executesql N'SELECT TOP(1) [a].[Id], [a].[ConcurrencyStamp], [a].[Description], [a].[Name], [a].[NormalizedName]  FROM [AspNetRoles] AS [a]  WHERE [a].[NormalizedName] = @__normalizedName_0'
93	rpc_completed	exec sp_executesql N'SELECT [a].[ClaimType], [a].[ClaimValue]  FROM [AspNetRoleClaims] AS [a]  WHERE [a].[RoleId] = @__role_Id_0'
97	rpc_completed	exec sp_executesql N'SELECT TOP(1) [a].[Id], [a].[ConcurrencyStamp], [a].[Description], [a].[Name], [a].[NormalizedName]  FROM [AspNetRoles] AS [a]  WHERE [a].[NormalizedName] = @__normalizedName_0'
...
repeated X number of roles
...

In scope

Provide a way to opt out of querying role claims.

UserClaimsPrincipalFactory.cs GenerateClaimsAsync protected override async Task<ClaimsIdentity> GenerateClaimsAsync(TUser user)

Alternatively, create a new method for IRoleClaimStore.GetClaimsAsync that accepts a collection of Roles instead of a single role so that the operation can be done in bulk.

RoleManager.cs GetClaimsAsync L387 public virtual Task<IList<Claim>> GetClaimsAsync(TRole role)

RoleStore GetClaimsAsync L363 public virtual async Task<IList<Claim>> GetClaimsAsync(TRole role, CancellationToken cancellationToken = default(CancellationToken))

Risks / unknowns

Examples

Current: return await RoleClaims.Where(rc => rc.RoleId.Equals(role.Id)).Select(c => new Claim(c.ClaimType!, c.ClaimValue!)).ToListAsync(cancellationToken);

Proposed bulk query:

return await RoleClaims.Where(rc => roles.Contains(rc.RoleId))
            .Select(c => new Claim(c.ClaimType!, c.ClaimValue!))
            .ToListAsync(cancellationToken);

[ghost]

Thanks for contacting us.

We're moving this issue to the .NET 8 Planning milestone for future evaluation / consideration. We would like to keep this around to collect more feedback, which can help us with prioritizing this work. We will re-evaluate this issue, during our next planning meeting(s). If we later determine, that the issue has no community involvement, or it's very rare and low-impact issue, we will close it - so that the team can focus on more important and high impact issues. To learn more about what to expect next and how this issue will be handled you can read more about our triage process here.

[mkArtakMSFT]

Hi. Thanks for contacting us. We're closing this issue as there was not much community interest in this ask for quite a while now. You can learn more about our triage process and how we handle issues by reading our Triage Process writeup.