Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ASP.Net Identity running multiple queries on every request [closed]

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();
    }
}
like image 772
James Hill Avatar asked Jan 12 '18 14:01

James Hill


1 Answers

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.

like image 89
James Hill Avatar answered Nov 15 '22 14:11

James Hill