在使用时，是否可以在DbContext核心中使用切分信息注入ASP.NET？

Question

我想使用Azure SQL Elastic Database Client库来管理我的ASP.NET核心应用程序中的Server分片。

DbContext目前被注入到我的服务中。

据我所知，我需要向我的DbContext类添加一个构造函数，该构造函数接受依赖于数据的路由所需的参数(即切分映射和切分键)。

如何在注入上下文时设置这些值？

Answer 1

是的，使用截取器的EF Core是可能的，并且工作得很好：

https://learn.microsoft.com/en-us/ef/core/logging-events-diagnostics/interceptors

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;

namespace <blah>;

public class RowLevelSecuritySqlInterceptor : DbCommandInterceptor, IRowLevelSecuritySqlInterceptor
{
    public Guid? TenantId { get; set; }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        SetSessionContext(command);

        return base.ReaderExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new ())
    {
        SetSessionContext(command);

        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result)
    {
        SetSessionContext(command);

        return base.NonQueryExecuting(command, eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = new ())
    {
        SetSessionContext(command);

        return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        SetSessionContext(command);

        return base.ScalarExecuting(command, eventData, result);
    }
    public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result,
        CancellationToken cancellationToken = new ())
    {
        SetSessionContext(command);

        return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    private void SetSessionContext(IDbCommand command)
    {
        var tenantId = TenantId is null ? "null" : $"'{TenantId.Value}'";
        command.CommandText = $"EXEC sp_set_session_context @key=N'TenantId', @value={tenantId};" + command.CommandText;
    }
}

可以为DI创建一个接口：

using Microsoft.EntityFrameworkCore.Diagnostics;

namespace <blah>;

public interface IRowLevelSecuritySqlInterceptor : IDbCommandInterceptor
{
    Guid? TenantId { get; set; }
}

并将其注入DI容器：

services.TryAddTransient<IRowLevelSecuritySqlInterceptor, RowLevelSecuritySqlInterceptor>();

您的DbContext可能看起来类似于：

public partial class MyDbContext
{
    private readonly IRowLevelSecuritySqlInterceptor _rowLevelSecuritySqlInterceptor;
    ...

    public AccountServiceDbContext(
        ...,
        IRowLevelSecuritySqlInterceptor rowLevelSecuritySqlInterceptor) : base(options)
    {
        ...,
        _rowLevelSecuritySqlInterceptor = rowLevelSecuritySqlInterceptor;
    }

    ...

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(...)
            .AddInterceptors(_rowLevelSecuritySqlInterceptor, ... and other interceptors);
    }
}