余小章 @ 大內殿堂

資料庫已經存在，在不同的資料庫，有相同的資料結構，應用程式必須要透過不同的參數來決定要指向哪一台資料庫執行個體?哪一個資料庫?哪一個表?

下圖是這次要處理的目標，我預期，目標資料庫結構如下圖：

 

資料表結構

use MemberDb01
go

create table dbo.Member02
(
    Id         varchar(50)    not null
        constraint Member02_pk
            primary key,
    Name       nvarchar(20),
    Age        int,
    SequenceId bigint identity,
    CreatedAt  datetimeoffset not null,
    CreatedBy  nvarchar(50),
    ChangedAt  datetimeoffset,
    ChangedBy  nvarchar(20),
    Email      varchar(50)
)
go

 

開發環境

• Windows 11 Home
• Rider 2024.2.7

實作

dotnet ef dbcontext scaffold 產生 EF Entity

若是要要透過 dotnet ef dbcontext scaffold 產生 EF Entity，必須要賦予正確的名字

ef-codegen-member:
  desc: EF Core 反向工程產生 MemberDbContext EF Entities
  dir: "src/Lab.Sharding.DB"
  cmds:
    - dotnet ef dbcontext scaffold "$SYS_DATABASE_CONNECTION_STRING1" Microsoft.EntityFrameworkCore.SqlServer -o AutoGenerated/Entities -c MemberDbContext --context-dir AutoGenerated/ -n Lab.Sharding.DB -t Member01 --force --no-onconfiguring --use-database-names

 

資料表就會像這樣，如果有 10 資料表，就會有 10 個不同名稱的類別，這樣直接用應該也不會有太大的問題，只是程式碼會多了很多的判斷，若再扯上關連可能會變得更複雜

public partial class Member01
{
    public string Id { get; set; } = null!;

    public string? Name { get; set; }

    public int? Age { get; set; }

    public long SequenceId { get; set; }

    public DateTimeOffset CreatedAt { get; set; }

    public string? CreatedBy { get; set; }

    public DateTimeOffset? ChangedAt { get; set; }

    public string? ChangedBy { get; set; }

    public string? Email { get; set; }
}

public partial class MemberDbContext : DbContext
{
    public MemberDbContext(DbContextOptions<MemberDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Member01> Member01s { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Member01>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("Member01_pk");

            entity.ToTable("Member01");

            entity.Property(e => e.Id)
                .HasMaxLength(50)
                .IsUnicode(false);
            entity.Property(e => e.ChangedBy).HasMaxLength(20);
            entity.Property(e => e.CreatedBy).HasMaxLength(50);
            entity.Property(e => e.Email)
                .HasMaxLength(50)
                .IsUnicode(false);
            entity.Property(e => e.Name).HasMaxLength(20);
            entity.Property(e => e.SequenceId).ValueGeneratedOnAdd();
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

 

變更 entity

接下來把產生出來的 class 結構調整了一下，Member01 => Member

public partial class Member
{
    省…
}

 

entity.ToTable($"Member01") => entity.ToTable($"Member{this.TablePostfix}")

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Member>(entity =>
    {
       entity.HasKey(e => e.Id).HasName($"Member{this.TablePostfix}_pk");
       entity.ToTable($"Member{this.TablePostfix}");
       省...
    });
}

 

另外建立一個 DynamicMemberDbContext 取代掉 MemberDbContext 

using Lab.Sharding.DB;
using Lab.Sharding.DB.AutoGenerated.Entities;
using Microsoft.EntityFrameworkCore;

public abstract class DynamicDbContext : DbContext
{
}

public class DynamicMemberDbContext : DynamicDbContext
{
    public string TablePostfix { get; }
    public string ConnectionString { get; }

    public DbSet<Member> Members { get; set; }

    public DynamicMemberDbContext(string connectionString, string tablePostfix)
    {
       this.ConnectionString = connectionString;
       this.TablePostfix = tablePostfix;
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
       optionsBuilder.UseSqlServer(this.ConnectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
       modelBuilder.Entity<Member>(entity =>
       {
          entity.HasKey(e => e.Id).HasName($"Member{this.TablePostfix}_pk");
          entity.ToTable($"Member{this.TablePostfix}");

          entity.Property(e => e.Id)
             .HasMaxLength(50)
             .IsUnicode(false);
          entity.Property(e => e.ChangedBy).HasMaxLength(20);
          entity.Property(e => e.CreatedBy).HasMaxLength(50);
          entity.Property(e => e.Email)
             .HasMaxLength(50)
             .IsUnicode(false);
          entity.Property(e => e.Name).HasMaxLength(20);
          entity.Property(e => e.SequenceId).ValueGeneratedOnAdd();
       });
    }
}

 

DynamicDbContextFactory 建立 DbContext

public interface IDynamicDbContextFactory<TContext>
    where TContext : DynamicDbContext
{
    TContext CreateDbContext(string serverName,string databaseName, string tablePostfix);
}

public class DynamicDbContextFactory<TContext>(IConnectionStringProvider connectionStringProvider)
    : IDynamicDbContextFactory<TContext>
    where TContext : DynamicDbContext
{
    public TContext CreateDbContext(string serverName,string databaseName, string tablePostfix)
    {
       var connectionString = connectionStringProvider.GetConnectionString(serverName, databaseName);
       return (TContext)Activator.CreateInstance(typeof(TContext), connectionString, tablePostfix);
    }
}

 

ConnectionStringProvider 存放連線字串

public interface IConnectionStringProvider
{
    string GetConnectionString(string serverName, string databaseName);

    void SetConnectionStrings(Dictionary<string, string> connectionStrings);
}

public class ConnectionStringProvider : IConnectionStringProvider
{
    private Dictionary<string, string> _connectionStrings;

    public void SetConnectionStrings(Dictionary<string, string> connectionStrings)
    {
       this._connectionStrings = connectionStrings;
    }

    public string GetConnectionString(string serverName, string databaseName)
    {
       if (this._connectionStrings.TryGetValue(serverName, out var connectionString) == false)
       {
          throw new ArgumentException("Unknown database identifier");
       }

       connectionString = $"{connectionString};Database={databaseName}";

       return connectionString;
    }
}

 

DI Container 依照 Key 定義連線字串

builder.Services.AddSingleton<IDynamicDbContextFactory<DynamicMemberDbContext>, DynamicDbContextFactory<DynamicMemberDbContext>>();
builder.Services.AddSingleton<IConnectionStringProvider, ConnectionStringProvider>(p =>
{
    var connectionStringProvider = new ConnectionStringProvider();
    connectionStringProvider.SetConnectionStrings(new Dictionary<string, string>
    {
       {
          ServerNames.Server01.ToString(),
          p.GetService<SYS_DATABASE_CONNECTION_STRING1>().Value
       },
       {
          ServerNames.Server02.ToString(),
          p.GetService<SYS_DATABASE_CONNECTION_STRING2>().Value
       }
    });
    return connectionStringProvider;
});

 

環境變數 local.env 

local.env 內容如下，SYS_DATABASE_CONNECTION_STRING1、SYS_DATABASE_CONNECTION_STRING2 分別代表不同的 Server

#sql server connection string
ASPNETCORE_ENVIRONMENT=Development
SYS_DATABASE_CONNECTION_STRING1=Server=localhost;User Id=SA;Password=pass@w0rd1~;TrustServerCertificate=True
SYS_DATABASE_CONNECTION_STRING2=Server=localhost;User Id=SA;Password=pass@w0rd1~;TrustServerCertificate=True
SYS_REDIS_URL=localhost:6379
EXTERNAL_API=http://localhost:5000/api

 

動態建立 DbContext

最後在操作資料庫的物件開一個洞 IDynamicDbContextFactory<DynamicMemberDbContext> dynamicDbContextFactory，選擇 Server、DatabaseName、TableName，就可以產生這一次要操作的 DbContext 了

public class MemberRepository(
    IDynamicDbContextFactory<DynamicMemberDbContext> dynamicDbContextFactory,
    省...)
{
    public async Task<PaginatedList<GetMemberResponse>>
       GetMembersAsync(int pageIndex, int pageSize, bool noCache = false, CancellationToken cancel = default)
    {
       省...

       var serverName = nameof(ServerNames.Server01);
       var dbName = nameof(DbNames.MemberDb01);

       await using var dbContext = dynamicDbContextFactory.CreateDbContext(serverName,
                                                          dbName,
                                                          "01");
       var selector = dbContext.Members
          .Select(p => new GetMemberResponse { Id = p.Id, Name = p.Name, Age = p.Age, Email = p.Email })
          .AsNoTracking();

       var totalCount = selector.Count();
       var paging = selector.OrderBy(p => p.Id)
          .Skip(pageIndex * pageSize)
          .Take(pageSize);
       var data = await paging
          .TagWith($"{nameof(MemberRepository)}.{nameof(this.GetMembersAsync)}")
          .ToListAsync(cancel);
       result = new PaginatedList<GetMemberResponse>(data, pageIndex, pageSize, totalCount);
       cachedData = JsonSerializer.Serialize(result, jsonSerializerOptions);
       cache.SetStringAsync(key, cachedData,
                       new DistributedCacheEntryOptions
                       {
                          AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5) //最好從組態設定讀取
                       }, cancel);

       return result;
    }
}

完整位置：https://github.com/yaochangyu/sample.dotblog/blob/398870aa5df2860593e273cbbbb51eb547d31026/ORM/EFCore/Lab.Sharding/src/Lab.Sharding.WebAPI/Member/MemberRepository.cs#L10-L10

心得

實作的過程當中一直想要使用 AddDbContextFactory 在 DI Container 處理，多花了一點研究時間，目前這個版本堪用，但可以再實作 DbContext Pool 來減少 DbContext 物件的開銷

範例位置

sample.dotblog/ORM/EFCore/Lab.Sharding at 398870aa5df2860593e273cbbbb51eb547d31026 · yaochangyu/sample.dotblog

補充

不死心，再寫了一版具有 HttpRequest Scope 的 DbContext Pool

public class RequestScopedDynamicDbContextFactory<TContext>(
    IConnectionStringProvider connectionStringProvider,
    IHttpContextAccessor httpContextAccessor)
    : IDynamicDbContextFactory<TContext>
    where TContext : DynamicDbContext
{
    public TContext CreateDbContext(string serverName, string databaseName, string tablePostfix)
    {
       var httpContext = httpContextAccessor.HttpContext;
       if (httpContext == null)
       {
          throw new InvalidOperationException("HttpContext is not available");
       }

       var name = (typeof(RequestScopedDynamicDbContextFactory<TContext>)).Name;
       var contextDictionary = httpContext.Items.GetOrCreate(
          name,
          () => new ConcurrentDictionary<string, TContext>());

       var requestId = httpContext.Items.GetOrCreate("RequestId", () => Guid.NewGuid().ToString());

       return contextDictionary.GetOrAdd(requestId, key =>
       {
          var connectionString = connectionStringProvider.GetConnectionString(serverName, databaseName);

          // var scope = serviceScopeFactory.CreateScope();
          var scope = httpContextAccessor.HttpContext.RequestServices.CreateScope();

          // 在請求結束時釋放資源
          httpContext.Response.RegisterForDispose(scope);

          var options = new DbContextOptionsBuilder<TContext>()
             .UseSqlServer(connectionString)
             .Options;

          return (TContext)Activator.CreateInstance(
             typeof(TContext),
             connectionString,
             tablePostfix);
       });
    }
}

https://github.com/yaochangyu/sample.dotblog/blob/d58e3d2b0c7533fec978b7820f48b8b2d68b091c/ORM/EFCore/Lab.Sharding/src/Lab.Sharding.DB/RequestScopedDynamicDbContextFactory.cs#L8-L23

若有謬誤，煩請告知，新手發帖請多包涵

Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET