ASP.NET Core WebApp多数据库适配及客户分库访问方案问询

Great question! Handling multi-tenant database isolation in ASP.NET Core is exactly the kind of scenario that comes up often with SaaS-style apps, and there are a few robust approaches that fit your requirements perfectly. Let's walk through the best solutions for your case:

The core idea here is to avoid hardcoding a single connection string for your DbContext and instead resolve the correct database connection dynamically for each incoming request. Here's how to implement this:

Step 1: Store Tenant Connection Strings #

First, add all your tenant-specific connection strings to appsettings.json (or a centralized config store like Azure App Configuration for scalability):

"ConnectionStrings": {
  "Database_Core": "Server=YOUR_SERVER;Database=Database_Core;Trusted_Connection=True;Encrypt=False;",
  "Database_Test": "Server=YOUR_SERVER;Database=Database_Test;Trusted_Connection=True;Encrypt=False;",
  "Customer_Alpha": "Server=YOUR_SERVER;Database=Customer_Alpha_DB;Trusted_Connection=True;Encrypt=False;",
  "Customer_Bravo": "Server=YOUR_SERVER;Database=Customer_Bravo_DB;Trusted_Connection=True;Encrypt=False;"
}

Step 2: Create a Tenant Connection Resolver #

Build a scoped service to identify the current tenant and fetch the matching connection string. The tenant identifier can come from authenticated user claims, subdomains, or request headers—pick the method that fits your app's flow:

public interface ITenantConnectionResolver
{
    string GetCurrentConnectionString();
}

public class TenantConnectionResolver : ITenantConnectionResolver
{
    private readonly IHttpContextAccessor _httpContextAccessor;
    private readonly IConfiguration _configuration;

    public TenantConnectionResolver(IHttpContextAccessor httpContextAccessor, IConfiguration configuration)
    {
        _httpContextAccessor = httpContextAccessor;
        _configuration = configuration;
    }

    public string GetCurrentConnectionString()
    {
        // Example: Get tenant ID from authenticated user's claims (adjust based on your auth setup)
        var tenantId = _httpContextAccessor.HttpContext?.User?.Claims
            .FirstOrDefault(c => c.Type == "TenantId")?.Value;

        // Fallback logic: Use Test for dev, Core for production if no tenant is identified
        var connectionStringName = string.IsNullOrEmpty(tenantId)
            ? (Environment.IsDevelopment() ? "Database_Test" : "Database_Core")
            : $"Customer_{tenantId}";

        var connectionString = _configuration.GetConnectionString(connectionStringName);
        if (string.IsNullOrEmpty(connectionString))
            throw new InvalidOperationException($"No connection string found for tenant: {tenantId}");

        return connectionString;
    }
}

Step 3: Register Services and Dynamic DbContext #

Update your Program.cs to register the resolver and configure the DbContext to use dynamic connections:

// Register access to the current HTTP context
builder.Services.AddHttpContextAccessor();

// Register the tenant connection resolver as a scoped service
builder.Services.AddScoped<ITenantConnectionResolver, TenantConnectionResolver>();

// Configure DbContext to resolve the connection string dynamically per request
builder.Services.AddDbContext<DashboardContext>((serviceProvider, options) =>
{
    var resolver = serviceProvider.GetRequiredService<ITenantConnectionResolver>();
    var connectionString = resolver.GetCurrentConnectionString();
    options.UseSqlServer(connectionString);
});

Choose the tenant identification method that aligns with your app's architecture:

• Claims-Based Authentication: Embed the tenant ID in JWT tokens or cookie claims when users log in. This is ideal for apps requiring user authentication.
• Subdomain Routing: Parse the subdomain (e.g., alpha.yourapp.com) to get the tenant ID. Great for public-facing SaaS apps where tenants have unique subdomains.
• Request Header/Query Parameter: Use a custom header (e.g., X-Tenant-Id) or query param for testing/internal tools—just add validation to prevent tampering in production.

Since you're using SSDT to keep schemas in sync, here's how to integrate it with your multi-tenant setup:

• Use your Database_Core SSDT project as the master schema template.
• Write an automated script or background service that creates a new tenant database by deploying the SSDT project whenever a new customer is onboarded.
• Add the new tenant's connection string to your config store automatically after database creation.

• Security: Ensure tenant identifiers can't be forged. For claims-based auth, validate JWT signatures; for subdomains, lock down DNS records to prevent spoofing.
• Connection Pooling: SQL Server automatically manages connection pools for distinct connection strings, so you don't have to worry about cross-tenant connection leaks.
• Migration Management: Use SSDT's publish pipelines to push schema updates to all tenant databases. Avoid EF Core migrations unless you're sure you can sync them across all tenants reliably.
• Development Workflow: Keep your Database_Test as the default for local development, and add a test tenant connection string to debug multi-tenant logic without affecting production data.

内容的提问来源于stack exchange，提问作者CBreeze