如何在实体框架连接上设置CONTEXT_INFO [英] How to set CONTEXT_INFO on Entity Framework connection

问题描述

我正在隔离基于Entity Framework的应用程序中共享数据库中的商店数据。我想使用SQL Server 2016行级安全性，但我希望数据库连接全部使用一个用户。因此，我想设置SQL Server CONTEXT_INFO 到我的所有实体框架查询的存储号。然后，RLS可以确定该行是否属于商店编号CONTEXT_INFO，设置为该行。

I'm working on isolating store data in a shared database in an Entity Framework-based application. I want to use SQL Server 2016 row-level security but I'd prefer to have my database connections all use a single user. So I want to set the SQL Server CONTEXT_INFO to a store number for all of my Entity Framework queries. Then RLS can determine if the row belongs to the store number CONTEXT_INFO is set to.

我要完成此操作的唯一想法是更新DbContext构造函数以创建连接本身，并在将连接传递给基本构造函数之前执行SET CONTEXT_INFO语句。我想我可以在部分类中为我的DbContext添加一个构造函数重载来做到这一点，或者修改t4模板以这种方式生成原始构造函数。

The only idea I have for accomplishing this is to update my DbContext constructor to create a connection itself and execute the SET CONTEXT_INFO statement before passing the connection on to the base constructor. I think I could either add a constructor overload in a partial class for my DbContext to do that, or modify the t4 template to generate the original constructor that way.

可以使用EF提供更好的方法来做到这一点？我首先使用数据库。

Does EF offer a better way to do this? I'm using DB first.

推荐答案

它看起来像是 IDbConnectionInterceptor 都能执行一些代码。这使我可以执行存储过程来设置 CONTEXT_INFO 或 SESSION_CONTEXT 变量。我的拦截器如下所示：

It looks like an implementer of IDbConnectionInterceptor is able to execute some code each time a connection is opened. That allows me to execute a stored procedure to set the CONTEXT_INFO or SESSION_CONTEXT variable. My interceptor looks like this:

public class MAContextInterceptor : IDbConnectionInterceptor
{
    private static ITenantIDProvider _tenantIDProvider;
    public MAContextInterceptor(ITenantIDProvider tenantIDProvider)
    {
        _tenantIDProvider = tenantIDProvider;
    }

    public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
    {
        // Set SESSION_CONTEXT to current tenant ID whenever EF opens a connection
        try
        {
            Guid tenantID = _tenantIDProvider == null ? Guid.Empty : _tenantIDProvider.GetTenantID();

            if (tenantID != Guid.Empty)
            {
                DbCommand cmd = connection.CreateCommand();
                cmd.CommandText = "ma_setcontextinfo";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                DbParameter param = cmd.CreateParameter();
                param.ParameterName = "@tenantID";
                param.Value = tenantID;
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
        }
        catch (System.NullReferenceException)
        {
            //log error
        }
    }
}

由于这是在数据层类库中，因此我添加了 ITenantIDProvider ，它允许上面的层提供提供租户ID的实现。最终在应用程序启动时在我的Global.asax中进行设置。它还调用 System.Data.Entity.Infrastructure.Interception.DbInterception.Add（new MAContextInterceptor（tenantIDProvider））; 注册拦截器。

Since this is in a data layer class library, I added an ITenantIDProvider which allows the layer above to provide an implementation that provides the tenant ID. This ultimately gets set in my Global.asax when the application starts. It also calls System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new MAContextInterceptor(tenantIDProvider)); to register the interceptor.

它似乎将应用于所有DbContext，我可以看到有问题，但出于我的目的，这可行。 该页面几乎完全拼写了出来。在问这个问题之前，我不知道为什么它没有出现在我的任何搜索中！

It looks like it will apply to all DbContexts, which I could see being problematic, but for my purposes this works. This page pretty much spelled it all out. I don't know why it didn't come up in any of my searches prior to asking this question!

这篇关于如何在实体框架连接上设置CONTEXT_INFO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！