asp.net - SignInManager.PasswordSignInAsync 生成许多数据库访问

Question

在我的应用程序中，我使用 ASP.NET Identity。一切都工作正常，但通过测试，我发现以下命令会产生许多数据库访问:SignInManager.PasswordSignInAsync:

var result = await SignInManager.PasswordSignInAsync(user.UserName, model.Password, model.RememberMe, shouldLockout: true);

我在使用性能测试来测试应用程序时注意到这个问题。测试表明，登录请求需要很长时间才能得到响应。下图是登录请求的性能测试结果: .

然后，我使用 SQL Server Profiler 检查执行命令 SignInManager.PasswordSignInAsync 时发生的情况。可以看出产生了大量的数据库访问。以下内容摘自 SQL Server Profiler:

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserFullName] AS [UserFullName], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[Online] AS [Online], 
    [Extent1].[LastOnlineDate] AS [LastOnlineDate], 
    [Extent1].[BrithDate] AS [BrithDate], 
    [Extent1].[Job] AS [Job], 
    [Extent1].[Gender] AS [Gender], 
    [Extent1].[CountryId] AS [CountryId], 
    [Extent1].[LivesIn] AS [LivesIn], 
    [Extent1].[RelationId] AS [RelationId], 
    [Extent1].[Religion] AS [Religion], 
    [Extent1].[FirstSchool] AS [FirstSchool], 
    [Extent1].[SecondSchool] AS [SecondSchool], 
    [Extent1].[University] AS [University], 
    [Extent1].[ContactInfo] AS [ContactInfo], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[EmailConfirmed] AS [EmailConfirmed], 
    [Extent1].[PasswordHash] AS [PasswordHash], 
    [Extent1].[SecurityStamp] AS [SecurityStamp], 
    [Extent1].[PhoneNumber] AS [PhoneNumber], 
    [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
    [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
    [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
    [Extent1].[LockoutEnabled] AS [LockoutEnabled], 
    [Extent1].[AccessFailedCount] AS [AccessFailedCount], 
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    WHERE ((UPPER([Extent1].[UserName])) = (UPPER(@p__linq__0))) OR ((UPPER([Extent1].[UserName]) IS NULL) AND (UPPER(@p__linq__0) IS NULL))',N'@p__linq__0 nvarchar(4000)',@p__linq__0=N'User1@gmail.com'

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[ClaimType] AS [ClaimType], 
    [Extent1].[ClaimValue] AS [ClaimValue]
    FROM [dbo].[AspNetUserClaims] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103

exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[ClaimType] AS [ClaimType], 
    [Extent1].[ClaimValue] AS [ClaimValue]
    FROM [dbo].[AspNetUserClaims] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103

exec sp_executesql N'SELECT 
    [Extent1].[LoginProvider] AS [LoginProvider], 
    [Extent1].[ProviderKey] AS [ProviderKey], 
    [Extent1].[UserId] AS [UserId]
    FROM [dbo].[AspNetUserLogins] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[RoleId] AS [RoleId]
    FROM [dbo].[AspNetUserRoles] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserFullName] AS [UserFullName], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[Online] AS [Online], 
    [Extent1].[LastOnlineDate] AS [LastOnlineDate], 
    [Extent1].[BrithDate] AS [BrithDate], 
    [Extent1].[Job] AS [Job], 
    [Extent1].[Gender] AS [Gender], 
    [Extent1].[CountryId] AS [CountryId], 
    [Extent1].[LivesIn] AS [LivesIn], 
    [Extent1].[RelationId] AS [RelationId], 
    [Extent1].[Religion] AS [Religion], 
    [Extent1].[FirstSchool] AS [FirstSchool], 
    [Extent1].[SecondSchool] AS [SecondSchool], 
    [Extent1].[University] AS [University], 
    [Extent1].[ContactInfo] AS [ContactInfo], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[EmailConfirmed] AS [EmailConfirmed], 
    [Extent1].[PasswordHash] AS [PasswordHash], 
    [Extent1].[SecurityStamp] AS [SecurityStamp], 
    [Extent1].[PhoneNumber] AS [PhoneNumber], 
    [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
    [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
    [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
    [Extent1].[LockoutEnabled] AS [LockoutEnabled], 
    [Extent1].[AccessFailedCount] AS [AccessFailedCount], 
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[ClaimType] AS [ClaimType], 
    [Extent1].[ClaimValue] AS [ClaimValue]
    FROM [dbo].[AspNetUserClaims] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT 
    [Extent1].[LoginProvider] AS [LoginProvider], 
    [Extent1].[ProviderKey] AS [ProviderKey], 
    [Extent1].[UserId] AS [UserId]
    FROM [dbo].[AspNetUserLogins] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103

    exec sp_executesql N'SELECT 
        [Extent1].[UserId] AS [UserId], 
        [Extent1].[RoleId] AS [RoleId]
        FROM [dbo].[AspNetUserRoles] AS [Extent1]
        WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
    exec sp_executesql N'SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[UserFullName] AS [UserFullName], 
        [Extent1].[UserId] AS [UserId], 
        [Extent1].[Online] AS [Online], 
        [Extent1].[LastOnlineDate] AS [LastOnlineDate], 
        [Extent1].[BrithDate] AS [BrithDate], 
        [Extent1].[Job] AS [Job], 
        [Extent1].[Gender] AS [Gender], 
        [Extent1].[CountryId] AS [CountryId], 
        [Extent1].[LivesIn] AS [LivesIn], 
        [Extent1].[RelationId] AS [RelationId], 
        [Extent1].[Religion] AS [Religion], 
        [Extent1].[FirstSchool] AS [FirstSchool], 
        [Extent1].[SecondSchool] AS [SecondSchool], 
        [Extent1].[University] AS [University], 
        [Extent1].[ContactInfo] AS [ContactInfo], 
        [Extent1].[Email] AS [Email], 
        [Extent1].[EmailConfirmed] AS [EmailConfirmed], 
        [Extent1].[PasswordHash] AS [PasswordHash], 
        [Extent1].[SecurityStamp] AS [SecurityStamp], 
        [Extent1].[PhoneNumber] AS [PhoneNumber], 
        [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
        [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
        [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
        [Extent1].[LockoutEnabled] AS [LockoutEnabled], 
        [Extent1].[AccessFailedCount] AS [AccessFailedCount], 
        [Extent1].[UserName] AS [UserName]
        FROM [dbo].[AspNetUsers] AS [Extent1]
        WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
    exec sp_executesql N'SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[UserId] AS [UserId], 
        [Extent1].[ClaimType] AS [ClaimType], 
        [Extent1].[ClaimValue] AS [ClaimValue]
        FROM [dbo].[AspNetUserClaims] AS [Extent1]
        WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT 
    [Extent1].[LoginProvider] AS [LoginProvider], 
    [Extent1].[ProviderKey] AS [ProviderKey], 
    [Extent1].[UserId] AS [UserId]
    FROM [dbo].[AspNetUserLogins] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[RoleId] AS [RoleId]
    FROM [dbo].[AspNetUserRoles] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserFullName] AS [UserFullName], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[Online] AS [Online], 
    [Extent1].[LastOnlineDate] AS [LastOnlineDate], 
    [Extent1].[BrithDate] AS [BrithDate], 
    [Extent1].[Job] AS [Job], 
    [Extent1].[Gender] AS [Gender], 
    [Extent1].[CountryId] AS [CountryId], 
    [Extent1].[LivesIn] AS [LivesIn], 
    [Extent1].[RelationId] AS [RelationId], 
    [Extent1].[Religion] AS [Religion], 
    [Extent1].[FirstSchool] AS [FirstSchool], 
    [Extent1].[SecondSchool] AS [SecondSchool], 
    [Extent1].[University] AS [University], 
    [Extent1].[ContactInfo] AS [ContactInfo], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[EmailConfirmed] AS [EmailConfirmed], 
    [Extent1].[PasswordHash] AS [PasswordHash], 
    [Extent1].[SecurityStamp] AS [SecurityStamp], 
    [Extent1].[PhoneNumber] AS [PhoneNumber], 
    [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
    [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
    [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
    [Extent1].[LockoutEnabled] AS [LockoutEnabled], 
    [Extent1].[AccessFailedCount] AS [AccessFailedCount], 
    [Extent1].[UserName] AS [UserName]
    FROM [dbo].[AspNetUsers] AS [Extent1]
    WHERE [Extent1].[Id] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103
exec sp_executesql N'SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[ClaimType] AS [ClaimType], 
    [Extent1].[ClaimValue] AS [ClaimValue]
    FROM [dbo].[AspNetUserClaims] AS [Extent1]
    WHERE [Extent1].[UserId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2103

以上查询只是结果的一部分。相同的查询会执行多次。这是正常情况还是有问题？

如果有问题，如何解决。

Answer 1

我假设您正在使用“现有的”身份设置。在这种情况下，您也可能会使用 Entity Framework 。

回答你的问题:根据我的经验，位于 UserManager、SignInManager 等后面的 UserStore 在执行操作时会被多次访问。

我注意到的主要问题是，使用 EntityFramework 时速度非常慢。我发现，如果我创建自己的 CustomUserStore，它会显着加快登录/用户操作速度。这些方法仍然有多个调用，但性能要好得多。

就我而言，我使用 Micro-ORM Dapper.NET 来驱动我的新 UserStore。 Dapper 执行查询的速度比 EntityFramework 快得多(根据某些测试中的查询，快 3-10 倍 - 请参阅:https://www.exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/)

Dapper.NET:https://github.com/StackExchange/Dapper

通过自定义 UserStore 重写，您可以在网上找到大量示例，但这里是一个:https://markjohnson.io/articles/exorcising-entity-framework-from-asp-net-identity/