ASP.NET Core 在用户登录时更改 EF 连接字符串 [英] ASP.NET Core change EF connection string when user logs in

问题描述

经过几个小时的研究，发现没有办法做到这一点；是时候提出问题了.

After a few hours of research and finding no way to do this; it's time to ask the question.

我有一个使用 EF Core 和 MVC 的 ASP.NET Core 1.1 项目，供多个客户使用.每个客户都有自己的数据库，其架构完全相同.该项目目前是一个正在迁移到网络的 Windows 应用程序.在登录屏幕上，用户有三个字段，公司代码、用户名和密码.我需要能够在用户尝试根据他们在公司代码输入中键入的内容登录时更改连接字符串，然后在整个会话期间记住他们的输入.

I have an ASP.NET Core 1.1 project using EF Core and MVC that is used by multiple customers. Each customer has their own database with the exact same schema. This project is currently a Windows application being migrated to the web. At the login screen the user has three fields, Company Code, Username and Password. I need to be able to change the connection string when the user attempts to login based on what they type in the Company Code input then remember their input throughout the session duration.

我找到了一些方法来使用一个数据库和多个模式来做到这一点，但没有找到使用相同模式的多个数据库的方法.

I found some ways to do this with one database and multiple schema, but none with multiple databases using the same schema.

我解决这个问题的方式并不是问题的实际解决方案，而是一种对我有用的解决方法.我的数据库和应用程序托管在 Azure 上.我对此的解决方法是将我的应用服务升级到支持插槽的计划(5 个插槽每月只需额外支付 20 美元).每个插槽都有相同的程序，但保存连接字符串的环境变量是公司特定的.通过这种方式，如果我愿意，我还可以对每个公司访问的子域进行子域.虽然这种方法可能不是其他人会做的，但它对我来说是最具成本效益的.发布到每个插槽比花时间做其他不正确的编程要容易.在 Microsoft 使更改连接字符串变得容易之前，这是我的解决方案.

The way I solved this problem isn't an actual solution to the problem, but a work around that worked for me. My databases and app are hosted on Azure. My fix to this was to upgrade my app service to a plan that supports slots (only an extra $20 a month for 5 slots). Each slot has the same program but the environment variable that holds the connection string is company specific. This way I can also subdomain each companies access if I want. While this approach may not be what others would do, it was the most cost effective to me. It is easier to publish to each slot than to spend the hours doing the other programming that doesn't work right. Until Microsoft makes it easy to change the connection string this is my solution.

回应 Herzl 的回答

这似乎可以工作.我试图让它实施.我正在做的一件事是使用访问我的上下文的存储库类.我的控制器将存储库注入其中以调用存储库中访问上下文的方法.我如何在存储库类中执行此操作.我的存储库中没有 OnActionExecuting 重载.此外，如果这在会话中持续存在，那么当用户再次打开浏览器访问应用程序并且仍然使用持续 7 天的 cookie 登录时会发生什么?这不是新会议吗?听起来应用程序会抛出异常，因为会话变量将为空，因此没有完整的连接字符串.我想我也可以将它存储为 Claim 并在 session 变量为 null 时使用 Claim.

This seems like it could work. I have tried to get it implemented. One thing I am doing though is using a repository class that accesses my context. My controllers get the repository injected into them to call methods in the repository that access the context. How do I do this in a repository class. There is no OnActionExecuting overload in my repository. Also, if this persists for the session, what happens when a user opens their browser to the app again and is still logged in with a cookie that lasts 7 days? Isn't this a new session? Sounds like the app would throw an exception because the session variable would be null and therefor not have a complete connection string. I guess I could also store it as a Claim and use the Claim if the session variable is null.

这是我的存储库类.IDbContextService 是 ProgramContext 但我开始添加您的建议以尝试使其工作.

Here is my repository class. IDbContextService was ProgramContext but I started adding your suggestions to try and get it to work.

public class ProjectRepository : IProjectRepository
{
    private IDbContextService _context;
    private ILogger<ProjectRepository> _logger;
    private UserManager<ApplicationUser> _userManager;

    public ProjectRepository(IDbContextService context,
                            ILogger<ProjectRepository> logger,
                            UserManager<ApplicationUser> userManger)
    {
        _context = context;
        _logger = logger;
        _userManager = userManger;
    }

    public async Task<bool> SaveChangesAsync()
    {
        return (await _context.SaveChangesAsync()) > 0;
    }
}

回应 The FORCE JB 的回答

我尝试实施您的方法.我在 Program.cs 中在线遇到异常

I tried to implement your approach. I get an exception in Program.cs on line

host.Run();

这是我的Program.cs"课程.原封不动.

Here is my 'Program.cs' class. Untouched.

using System.IO;
using Microsoft.AspNetCore.Hosting;

namespace Project
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var host = new WebHostBuilder()
                .UseKestrel()
                .UseContentRoot(Directory.GetCurrentDirectory())
                .UseIISIntegration()
                .UseStartup<Startup>()
                .Build();

            host.Run();
        }
    }
}

还有我的Startup.cs"类.

And my 'Startup.cs' class.

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json.Serialization;
using System;
using System.Collections.Generic;
using Project.Entities;
using Project.Services;

namespace Project
{
    public class Startup
    {
        private IConfigurationRoot _config;

        public Startup(IHostingEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json")
                .AddEnvironmentVariables();

            _config = builder.Build();
        }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton(_config);
            services.AddIdentity<ApplicationUser, IdentityRole>(config =>
            {
                config.User.RequireUniqueEmail = true;
                config.Password.RequireDigit = true;
                config.Password.RequireLowercase = true;
                config.Password.RequireUppercase = true;
                config.Password.RequireNonAlphanumeric = false;
                config.Password.RequiredLength = 8;
                config.Cookies.ApplicationCookie.LoginPath = "/Auth/Login";
                config.Cookies.ApplicationCookie.ExpireTimeSpan = new TimeSpan(7, 0, 0, 0); // Cookies last 7 days
            })
            .AddEntityFrameworkStores<ProjectContext>();
            services.AddScoped<IUserClaimsPrincipalFactory<ApplicationUser>, AppClaimsPrincipalFactory>();
            services.AddScoped<IProjectRepository, ProjectRepository>();
            services.AddTransient<MiscService>();
            services.AddLogging();
            services.AddMvc()
            .AddJsonOptions(config =>
            {
                config.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
            });
        }

        public void Configure(IApplicationBuilder app, ILoggerFactory loggerFactory)
        {
            Dictionary<string, string> connStrs = new Dictionary<string, string>();
            connStrs.Add("company1", "1stconnectionstring"));
            connStrs.Add("company2", "2ndconnectionstring";
            DbContextFactory.SetDConnectionString(connStrs);
            //app.UseDefaultFiles();

            app.UseStaticFiles();
            app.UseIdentity();
            app.UseMvc(config =>
            {
                config.MapRoute(
                    name: "Default",
                    template: "{controller}/{action}/{id?}",
                    defaults: new { controller = "Auth", action = "Login" }
                    );
            });
        }
    }
}

还有例外:

InvalidOperationException: Unable to resolve service for type 'Project.Entities.ProjectContext' while attempting to activate 'Microsoft.AspNetCore.Identity.EntityFrameworkCore.UserStore`4[Project.Entities.ApplicationUser,Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityRole,Project.Entities.ProjectContext,System.String]'.

不知道在这里做什么.

部分成功编辑

好的，我让你的例子工作了.我可以使用不同的 id 在我的存储库构造函数中设置连接字符串.我现在的问题是登录并选择正确的数据库.我想过从会话或声明中提取存储库，无论是非空的.但是我无法在登录控制器中使用 SignInManager 之前设置该值，因为 SignInManager 被注入到控制器中，该控制器在我更新会话变量之前创建了一个上下文.我能想到的唯一方法是进行两页登录.第一页将询问公司代码并更新会话变量.第二个页面将使用 SignInManager 并将存储库注入到控制器构造函数中.这会在第一页更新会话变量后发生.通过两个登录视图之间的动画，这实际上可能更具视觉吸引力.除非有人对没有两个登录视图的方法有任何想法，否则我将尝试实现两个页面登录并发布代码(如果有效).

Okay I got your example working. I can set the connection string in my repository constructor using a different id. My problem now is logging in and choosing the right database. I thought about having the repository pull from a session or claim, whatever wasn't null. But I can't set the value before using the SignInManager in the Login controller because SignInManager is injected into the controller which creates a context before I update the session variable. The only way I can think of is to have a two page login. The first page will ask for the company code and update the session variable. The second page will use the SignInManager and have the repository injected into the controllers constructor. This would happen after the first page updates the session variable. This may actually be more visually appealing with animations between both login views. Unless anyone has any ideas on a way to do this without two login views I am going to try and implement the two page login and post the code if it works.

其实已经坏了

当它工作时，那是因为我还有一个有效的 cookie.我会运行该项目，它会跳过登录.现在我在清除缓存后收到异常 InvalidOperationException: No database provider has been configured for this DbContext.我已经完成了这一切，并且正确创建了上下文.我的猜测是 Identity 有一些问题.以下代码在 ConfigureServices 中添加实体框架存储会导致问题吗?

When it was working, it is because I still had a valid cookie. I would run the project and it would skip the login. Now I get the exception InvalidOperationException: No database provider has been configured for this DbContext after clearing my cache. I have stepped through it all and the context is being created correctly. My guess is that Identity is having some sort of issues. Could the below code adding the entity framework stores in ConfigureServices be causing the issue?

services.AddIdentity<ApplicationUser, IdentityRole>(config =>
{
    config.User.RequireUniqueEmail = true;
    config.Password.RequireDigit = true;
    config.Password.RequireLowercase = true;
    config.Password.RequireUppercase = true;
    config.Password.RequireNonAlphanumeric = false;
    config.Password.RequiredLength = 8;
    config.Cookies.ApplicationCookie.LoginPath = "/Company/Login";
    config.Cookies.ApplicationCookie.ExpireTimeSpan = new TimeSpan(7, 0, 0, 0); // Cookies last 7 days
})
.AddEntityFrameworkStores<ProgramContext>();

编辑

我确认 Identity 是问题所在.我在执行 PasswordSignInAsync 之前从我的存储库中提取了数据，它提取的数据很好.如何为 Identity 创建 DbContext?

I verified Identity is the problem. I pulled data from my repository before executing PasswordSignInAsync and it pulled the data just fine. How is the DbContext created for Identity?

推荐答案

我已经很久没有发布这个问题了，我从来没有分享过我开发的解决方案，所以我想我应该这样做.

It's been a long time since I posted this question, and I never shared the solution I developed, so I figured I should.

我最终选择了为我的租户使用不同子域的路线.因此，我只是创建了一个 TenantService 来检查 url 并从 config 返回一个连接字符串.在我的 DbContext 的 OnConfiguring 方法中，我只是调用了租户服务并使用了返回的连接字符串.下面是一些示例代码:

I ended up going the route of using different subdomains for my tenants. Because of this, I simply created a TenantService that checked the url and returned a connection string from config. Inside my DbContext's OnConfiguring method, I simply called the tenant service and used the returned connection string. Here is some sample code:

租户服务

public class Tenant
{
    public string Name { get; set; }

    public string Hostname { get; set; }

    public string ConnectionString { get; set; }
}

public interface ITenantService
{
    Tenant GetCurrentTenant();

    List<Tenant> GetTenantList();
}

public class TenantService : ITenantService
{
    private readonly ILogger<TenantService> _logger;
    private readonly IHttpContextAccessor _httpContext;
    private readonly IConfiguration _configuration;

    public TenantService(
        ILogger<TenantService> logger,
        IHttpContextAccessor httpContext,
        IConfiguration configuration)
    {
        _logger = logger;
        _httpContext = httpContext;
        _configuration = configuration;
    }

    /// <summary>
    /// Gets the current tenant from the host.
    /// </summary>
    /// <returns>The tenant.</returns>
    public Tenant GetCurrentTenant()
    {
        Tenant tenant;
        var host = _httpContext.HttpContext.Request.Host;
        var tenants = GetTenantList();

        tenant = tenants.SingleOrDefault(t => t.Hostname == host.Value);
        if (tenant == null)
        {
            _logger.LogCritical("Could not find tenant from host: {host}", host);
            throw new ArgumentException($"Could not find tenant from host: {host}");
        }
        return tenant;
    }

    /// <summary>
    /// Gets a list of tenants in configuration.
    /// </summary>
    /// <returns>The list of tenants.</returns>
    public List<Tenant> GetTenantList()
    {
        var tenants = new List<Tenant>();

        _configuration.GetSection("Tenants").Bind(tenants);

        return tenants;
    }
}

数据库上下文

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    base.OnConfiguring(optionsBuilder);

    if (!optionsBuilder.IsConfigured)
    {
        if (_tenantService == null)
        {
            throw new ArgumentNullException(nameof(_tenantService));
        }
        optionsBuilder.UseSqlServer(_tenantService.GetCurrentTenant().ConnectionString);
    }
}

这篇关于ASP.NET Core 在用户登录时更改 EF 连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！