Home ASP.Net Identity running multiple queries on every request
Reply: 1

ASP.Net Identity running multiple queries on every request

James Hill
1#
James Hill Published in 2018-01-12 14:53:18Z

ASP.Net Identity is executing the queries below on every request. I have not changed any of the Identity code that was gen'd when my MVC project was created. My Startup.Auth.cs code is below.

Note that validateInterval is set to TimeSpan.FromMinutes(30). Also note that cookie expiration is being explicitly set (ExpireTimeSpan) is set to TimeSpan.FromMinutes(30). I found a similar question on SO whose answer indicated that TimeSpan.FromMinutes(30) should be sufficient to prevent ASP.Net Identity from reaching out to the DB on every request.

What am I missing here?

Startup.Auth.cs

public partial class Startup
{
 public void ConfigureAuth(IAppBuilder app)
 {
  // Configure the db context, user manager and signin manager to use a single instance per request
  app.CreatePerOwinContext(ApplicationDbContext.Create);
  app.CreatePerOwinContext < ApplicationUserManager > (ApplicationUserManager.Create);
  app.CreatePerOwinContext < ApplicationSignInManager > (ApplicationSignInManager.Create);

  // Enable the application to use a cookie to store information for the signed in user
  // and to use a cookie to temporarily store information about a user logging in with a third party login provider
  // Configure the sign in cookie
  app.UseCookieAuthentication(new CookieAuthenticationOptions
  {
   AuthenticationType = DefaultAuthenticationTypes.ApplicationCookie,
    LoginPath = new PathString("/Account/Login"),
    Provider = new CookieAuthenticationProvider
    {
     // Enables the application to validate the security stamp when the user logs in.
     // This is a security feature which is used when you change a password or add an external login to your account.  
     OnValidateIdentity = SecurityStampValidator
      .OnValidateIdentity < ApplicationUserManager, ApplicationUser > (
       validateInterval: TimeSpan.FromMinutes(30),
       regenerateIdentity: (manager, user) => user.GenerateUserIdentityAsync(manager)),
    }
    SlidingExpiration = true,
    ExpireTimeSpan = TimeSpan.FromMinutes(30)
  });
  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);
 }
}

Queries being executed on each request (standard ASP.Net Identity queries)

exec sp_executesql N'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',N'@p0 nvarchar(4000)',@p0=N'[ID]'

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 nvarchar(4000)',@p__linq__0=N'[ID]'

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 nvarchar(4000)',@p__linq__0=N'[ID]'

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 nvarchar(4000)',@p__linq__0=N'[ID]'

EDIT

I did make one change that I forgot to mention. We moved our Identity-related tables to a SQL DB. Changes below:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    // Changed connection string name
    public ApplicationDbContext()
        : base("MaestroConnection", throwIfV1Schema: false)
    {
    }

    public static ApplicationDbContext Create()
    {
        return new ApplicationDbContext();
    }
}
James Hill
2#
James Hill Reply to 2018-01-29 16:25:47Z

Alright, this one made me feel a bit foolish. After stepping through the code and noticing that regenerateIdentity: (manager, user) => user.GenerateUserIdentityAsync(manager)) was being called properly and not repeatedly, I did some further digging.

In the nav menu partial view, I found this line of code:

<a href="#" class="nav-link dropdown-toggle">@("@" + 
            ApplicationUser.CurrentUser.Email.Split('@')[0])</a>

Executing that code on every page load resulted in the 4 queries above being run. headdesk.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.305417 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO