登录时，ASP.NET Identity对每个页面请求执行数据库查询 [英] ASP.NET Identity executes database query on every page request when logged in
问题描述
我注意到，我的基于身份2.2.1的ASP.NET Web窗体应用程序在每个页面请求中都从数据库中获取登录的用户数据.这是正常的设计行为吗?出于性能原因，用户数据可能会缓存在Session中.我已仔细检查是否未在母版页中添加额外的代码，否则可能会导致此行为.但是，不，我使用的是标准Web表单模板，而没有将任何代码添加到母版页.
I have noticed that my ASP.NET Web Forms application based on Identity 2.2.1 fetches logged in user data from the database on every page request. Is this normal and designed behaviour? For performance reasons, user data might be cached in Session. I have double checked if I have not added extra code in master page which might cause this behaviour. But no, I am using standard Web Form template with no code added to the master page.
在每个页面请求上执行的SQL语句:
SQL statement executed on every page request:
SELECT
[Extent1].[Id] AS [Id],
[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] = @p0
更新1
由于应用程序可以使用App Service和Azure SQL在Azure中运行，因此每个页面请求背后的数据库查询证明都是Application Insights(根据随附的屏幕截图).
As application works in Azure using App Service and Azure SQL, proof for database query behind each page request was Application Insights, as per attached screenshot.
我已经开始进一步调查，并将数据库移至本地环境. SQL Server Profiler显示实际上每个页面请求上有10个对数据库的查询.这些是对AspNetUsers，AspNetUserClaims，AspNetUserLogins等的SELECT.其中一些被执行两次.这不依赖于母版页.不基于母版页的页面会触发与基于一个母版页的页面相同的10个查询.
I have started to investigate further and have moved database to local environment. SQL Server Profiler shows there is in fact 10 queries to database on each page request. Those are SELECT to AspNetUsers, AspNetUserClaims, AspNetUserLogins, etc. Some of them are executed twice. This does not depend on master page. Pages not based on master page trigger same 10 queries as those based on one.
根据下面的源代码，我对默认的Visual Studio模板做了一些修改.我已经仔细检查过，一旦用户登录，基于相同模板的新项目就不会触发任何数据库查询.
I have done few modifications to default Visual Studio template, as per below source code. I have double checked that new project based on same template does not trigger any database queries once user is logged in.
所做的修改:
- 其他字段，通过迁移添加到数据库表中
- 少量配置参数
- 电子邮件服务配置
ApplicationUser类中的源代码:
Global_asax
Global_asax
Public Class Global_asax
Inherits HttpApplication
Sub Application_Start(sender As Object, e As EventArgs)
' Fires when the application is started
RouteConfig.RegisterRoutes(RouteTable.Routes)
BundleConfig.RegisterBundles(BundleTable.Bundles)
End Sub
End Class
启动
Partial Public Class Startup
' For more information on configuring authentication, please visit http://go.microsoft.com/fwlink/?LinkId=301883
Public Sub ConfigureAuth(app As IAppBuilder)
'Configure the db context, user manager and signin manager to use a single instance per request
app.CreatePerOwinContext(AddressOf ApplicationDbContext.Create)
app.CreatePerOwinContext(Of ApplicationUserManager)(AddressOf ApplicationUserManager.Create)
app.CreatePerOwinContext(Of ApplicationSignInManager)(AddressOf ApplicationSignInManager.Create)
' Enable the application to use a cookie to store information for the signed in user
app.UseCookieAuthentication(New CookieAuthenticationOptions() With {
.AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
.Provider = New CookieAuthenticationProvider() With {
.OnValidateIdentity = SecurityStampValidator.OnValidateIdentity(Of ApplicationUserManager, ApplicationUser)(
validateInterval:=TimeSpan.FromMinutes(0),
regenerateIdentity:=Function(manager, user) user.GenerateUserIdentityAsync(manager))},
.LoginPath = New PathString("/Account/Login"),
.ExpireTimeSpan = TimeSpan.FromMinutes(20),
.SlidingExpiration = True})
' Use a cookie to temporarily store information about a user logging in with a third party login provider
'app.UseExternalSignInCookie(DefaultAuthenticationTypes.ExternalCookie)
' Enables the application to temporarily store user information when they are verifying the second factor in the two-factor authentication process.
'app.UseTwoFactorSignInCookie(DefaultAuthenticationTypes.TwoFactorCookie, TimeSpan.FromMinutes(5))
' Enables the application to remember the second login verification factor such as phone or email.
' Once you check this option, your second step of verification during the login process will be remembered on the device where you logged in from.
' This is similar to the RememberMe option when you log in.
'app.UseTwoFactorRememberBrowserCookie(DefaultAuthenticationTypes.TwoFactorRememberBrowserCookie)
' Uncomment the following lines to enable logging in with third party login providers
'app.UseMicrosoftAccountAuthentication(
' clientId:= "",
' clientSecret:= "")
'app.UseTwitterAuthentication(
' consumerKey:= "",
' consumerSecret:= "")
'app.UseFacebookAuthentication(
' appId:= "",
' appSecret:= "")
'app.UseGoogleAuthentication(New GoogleOAuth2AuthenticationOptions() With {
' .ClientId = "",
' .ClientSecret = ""})
End Sub
End Class
IdentityConfig.vb
IdentityConfig.vb
Public Class EmailService
Implements IIdentityMessageService
Public Function SendAsync(message As IdentityMessage) As Task Implements IIdentityMessageService.SendAsync
' Plug in your email service here to send an email.
'Return Task.FromResult(0)
Dim client As New Net.Mail.SmtpClient(DTAppSettings.SendGrid_SMTPServer, 587)
Dim credentials As New Net.NetworkCredential(DTAppSettings.SendGrid_Username, DTAppSettings.SendGrid_Password)
client.Credentials = credentials
client.EnableSsl = True
Dim mailmessage As New Net.Mail.MailMessage With {
.From = New Net.Mail.MailAddress(DTAppSettings.SendGrid_FromAddress, DTAppSettings.SendGrid_FromName),
.Subject = message.Subject,
.Body = message.Body,
.IsBodyHtml = True
}
mailmessage.To.Add(message.Destination)
Return client.SendMailAsync(mailmessage)
End Function
End Class
Public Class SmsService
Implements IIdentityMessageService
Public Function SendAsync(message As IdentityMessage) As Task Implements IIdentityMessageService.SendAsync
' Plug in your SMS service here to send a text message.
Return Task.FromResult(0)
End Function
End Class
' Configure the application user manager used in this application. UserManager is defined in ASP.NET Identity and is used by the application.
Public Class ApplicationUserManager
Inherits UserManager(Of ApplicationUser)
Public Sub New(store As IUserStore(Of ApplicationUser))
MyBase.New(store)
End Sub
Public Shared Function Create(options As IdentityFactoryOptions(Of ApplicationUserManager), context As IOwinContext) As ApplicationUserManager
Dim manager = New ApplicationUserManager(New UserStore(Of ApplicationUser)(context.[Get](Of ApplicationDbContext)()))
' Configure validation logic for usernames
manager.UserValidator = New UserValidator(Of ApplicationUser)(manager) With {
.AllowOnlyAlphanumericUserNames = False,
.RequireUniqueEmail = True
}
' Configure validation logic for passwords
manager.PasswordValidator = New PasswordValidator() With {
.RequiredLength = 6,
.RequireNonLetterOrDigit = True,
.RequireDigit = True,
.RequireLowercase = True,
.RequireUppercase = True
}
' Register two factor authentication providers. This application uses Phone and Emails as a step of receiving a code for verifying the user.
' You can write your own provider and plug in here.
'manager.RegisterTwoFactorProvider("Phone Code", New PhoneNumberTokenProvider(Of ApplicationUser)() With {
' .MessageFormat = "Your security code is {0}"
'})
'manager.RegisterTwoFactorProvider("Email Code", New EmailTokenProvider(Of ApplicationUser)() With {
' .Subject = "Security Code",
' .BodyFormat = "Your security code is {0}"
'})
' Configure user lockout defaults
manager.UserLockoutEnabledByDefault = True
manager.DefaultAccountLockoutTimeSpan = TimeSpan.FromMinutes(5)
manager.MaxFailedAccessAttemptsBeforeLockout = 5
manager.EmailService = New EmailService()
manager.SmsService = New SmsService()
Dim dataProtectionProvider = options.DataProtectionProvider
If dataProtectionProvider IsNot Nothing Then
manager.UserTokenProvider = New DataProtectorTokenProvider(Of ApplicationUser)(dataProtectionProvider.Create("ASP.NET Identity")) With {
.TokenLifespan = TimeSpan.FromHours(1)
}
End If
Return manager
End Function
End Class
Public Class ApplicationSignInManager
Inherits SignInManager(Of ApplicationUser, String)
Public Sub New(userManager As ApplicationUserManager, authenticationManager As IAuthenticationManager)
MyBase.New(userManager, authenticationManager)
End Sub
Public Overrides Function CreateUserIdentityAsync(user As ApplicationUser) As Task(Of ClaimsIdentity)
Return user.GenerateUserIdentityAsync(DirectCast(UserManager, ApplicationUserManager))
End Function
Public Shared Function Create(options As IdentityFactoryOptions(Of ApplicationSignInManager), context As IOwinContext) As ApplicationSignInManager
Return New ApplicationSignInManager(context.GetUserManager(Of ApplicationUserManager)(), context.Authentication)
End Function
End Class
推荐答案
问题与这段代码有关:
.OnValidateIdentity = SecurityStampValidator.OnValidateIdentity(Of ApplicationUserManager,
ApplicationUser)(validateInterval:=TimeSpan.FromMinutes(0),
尝试更大的值，例如.FromMinutes(15)
由于validateInterval为0，因此基本上是在每次页面加载时重新验证身份信息.
Since the validateInterval is 0, it's basically re-validating the Identity information on every page load.
这篇关于登录时，ASP.NET Identity对每个页面请求执行数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
