sql - ASP.NET Identity Core - 角色相关查询太多

Question

我的项目使用基于角色的授权，它有超过 100 个角色。我注意到在每次操作之前，服务器都会查询每个用户角色并分别声明。在每个操作之前有 200 多个查询。即使是空 Controller 也会这样做，所以我认为这是 ASP.NET Identity Core 的功能。有什么办法可以优化这个吗？

提前致谢。

ASP.NET Core web server output (one out of many role queries):

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__role_Id_0='390'], CommandType='Text', CommandTimeout='30']
      SELECT [rc].[ClaimType], [rc].[ClaimValue]
      FROM [AspNetRoleClaims] AS [rc]
      WHERE [rc].[RoleId] = @__role_Id_0
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@__normalizedName_0='100' (Size = 256)], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [r].[Id], [r].[ConcurrencyStamp], [r].[Name], [r].[NormalizedName]
      FROM [AspNetRoles] AS [r]
      WHERE [r].[NormalizedName] = @__normalizedName_0

我的 Startup.cs 类:

    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies 
                // is needed for a given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

            services.AddRouting(options => options.LowercaseUrls = true);
            services.AddDistributedMemoryCache();
            services.AddSession(options =>
            {
                options.IdleTimeout = TimeSpan.FromDays(1);
                options.Cookie.IsEssential = true;
            });

            services.AddDbContext<AppDbContext>(options =>
                options
                    .EnableSensitiveDataLogging()
                    .UseSqlServer(Configuration.GetConnectionString("DefaultConnection"), x =>
                    {
                        x.UseRowNumberForPaging();
                        x.UseNetTopologySuite();
                    }));

            services.Configure<WebEncoderOptions>(options => 
            {
                options.TextEncoderSettings = new TextEncoderSettings(UnicodeRanges.All);
            });

            services.Configure<AppConfiguration>(
                Configuration.GetSection("AppConfiguration"));

            services.AddIdentity<User, UserRole>()
                .AddEntityFrameworkStores<AppDbContext>()
                .AddDefaultTokenProviders();

            services.Configure<IdentityOptions>(options =>
            {
                // Password settings
                options.Password.RequireDigit = true;
                options.Password.RequiredLength = 8;
                options.Password.RequireNonAlphanumeric = false;
                options.Password.RequireUppercase = true;
                options.Password.RequireLowercase = false;
                options.Password.RequiredUniqueChars = 6;

                // Lockout settings
                options.Lockout.DefaultLockoutTimeSpan = TimeSpan.FromMinutes(30);
                options.Lockout.MaxFailedAccessAttempts = 10;
                options.Lockout.AllowedForNewUsers = true;

                // User settings
                options.User.RequireUniqueEmail = true;
            });

            services.Configure<SecurityStampValidatorOptions>(options =>
            {
                // enables immediate logout, after updating the users stat.
                options.ValidationInterval = TimeSpan.Zero;
            });

            services.ConfigureApplicationCookie(options =>
            {
                // Cookie settings
                options.Cookie.HttpOnly = true;
                options.Cookie.Expiration = TimeSpan.FromDays(150);
                // If the LoginPath isn't set, ASP.NET Core defaults 
                // the path to /Account/Login.
                options.LoginPath = "/Account/Login";
                // If the AccessDeniedPath isn't set, ASP.NET Core defaults 
                // the path to /Account/AccessDenied.
                options.AccessDeniedPath = "/Account/AccessDenied";
                options.SlidingExpiration = true;
            });

            // Add application services.
            services.AddScoped<IEmailSenderService, EmailSenderService>();
            services.AddScoped<IUploaderService, UploaderService>();
            services.AddScoped<IPdfService, PdfService>();
            services.AddScoped<ICurrencyRateService, CurrencyRateService>();
            services.AddScoped<IViewRenderService, ViewRenderService>();
            services.AddScoped<IUserCultureInfoService, UserCultureInfoService>();
            services.AddScoped<IUserService, UserService>();
            services.AddHostedService<QueuedHostedService>();
            services.AddSingleton<IBackgroundTaskQueue, BackgroundTaskQueue>();

            services
                .AddMvc(options =>
                {
                    options.EnableEndpointRouting = false;

                    options
                        .RegisterDateTimeProvider(services)
                        .ModelMetadataDetailsProviders
                        .Add(new BindingSourceMetadataProvider(typeof(ListFilterViewModel), BindingSource.ModelBinding));
                })
                .AddSessionStateTempDataProvider()
                .SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseDatabaseErrorPage();
                // app.UseMiddleware<StackifyMiddleware.RequestTracerMiddleware>();
            }
            else
            {
#if DEBUG
                app.UseDeveloperExceptionPage();
#else
                app.UseExceptionHandler("/Default/Error");
#endif

                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();
            app.UseSession();
            app.UseCookiePolicy();
            app.UseAuthentication();

            app.UseMvc(routes =>
            {
                routes.MapAreaRoute(
                    name: "Hubs",
                    areaName:"Hubs",
                    template: "Hubs/{controller=CompanyAddresses}/{action=Index}/{id?}");

                routes.MapRoute(
                    name: "areas",
                    template: "{area:exists}/{controller=Default}/{action=Index}/{id?}"
                );

                routes.MapRoute(
                    name: "default",
                    template: "{controller=Default}/{action=Index}/{id?}");
            });
        }
    }

Answer 1

我已经找出导致这种奇怪行为的原因。这是我的 Startup.cs 类中的这段代码:

services.Configure<SecurityStampValidatorOptions>(options =>
{
    // enables immediate logout, after updating the users stat.
    options.ValidationInterval = TimeSpan.Zero;
});

删除它解决了我的问题。我一直在使用它通过更新用户的安全标记来强制注销用户，如下所述:How to sign out other user in ASP.NET Core Identity

看来我将不得不寻找其他强制注销的解决方案，但我很高兴请求现在不会生成数百个 SQL 查询。