实体框架-使用IDbConnectionInterceptor设置session_context [英] Entity Framework - Setting session_context using IDbConnectionInterceptor

问题描述

我正在关注本教程，以便通过Entity Framework 6 CodeFirst在SQL Server中使用行级安全性。教程代码示例演示如何使用IDbConnectionInterceptor并在 session_context 中设置当前用户ID。要检索用户ID，它使用静态访问器方法 HttpContext.Current.User.Identity.GetUserId（），该方法与Asp.Net身份和System.Web命名空间结合在一起。 / p>

在我的多租户Web应用中，我想将 tenantId 注入到 DbConnectionInterceptor 使用Unity（不创建与 HttpContext 的硬耦合），并在 session_context 中设置tenantId。我发现 DbConnectionInterceptor 需要全局注册（例如，在应用程序启动时），因此您不能让Unity创建 DbConnectionInterceptor 每个请求的实例。

我的解决方案中还有2个DbContext，它们代表2个不同的数据库（Tenant数据库和系统数据库），我只想应用 session_context 到租户数据库。

似乎剩下的唯一选择就是将tenantId注入 DbContext 通过Unity进行访问，并访问 Opened（） DbContext 实例> DbConnectionInterceptor 的方法。为此，我想到了在 Opened（）方法中使用 interceptionContext 参数的方法。 interceptionContext 具有 DbContexts （复数）属性。没有关于此的文档，所以我认为这样的事情会起作用：

  public void Opened（DbConnection connection，DbConnectionInterceptionContext拦截上下文）
 {
 var firstDbContext = InterceptionContext.DbContexts.FirstOrDefault（d => d是TenantDataContext）; 
 if（firstDbContext！= null）
 {
 var dataContext = firstDbContext作为TenantDataContext; 
 var tenantId = dataContext.TenantId; 
 
 DbCommand cmd = connection.CreateCommand（）; 
 cmd.CommandText = $ EXEC sp_set_session_context @ key = N’TenantId’，@ value = {tenantId};;; 
 cmd.ExecuteNonQuery（）; 
} 
} 
  

我的代码检查DbContexts集合是否包含 TenantDataContext 作为第一个元素，并执行 sp_set_session_context 。但是我担心的是两个DbContext是否有可能同时存在？如果是这种情况，到我的其他数据库的连接也会设置我不需要的 session_context 。我想知道为什么Microsoft将其作为集合属性而不是单个 DbContext 属性提供。此属性使您想知道同一连接是否可以被多个DbContext使用。

有没有人实现了我想要的？

解决方案

我意识到这是一个比较老的问题，但我想我会发布我们为那些寻找解决方案的人提供的解决方案。
我们使用拦截器将SQLServer session_context语句注入通过EF运行的命令/连接中。

在我们的例子中，我们必须为DbCommand和DbConnection处理EF Linq查询和通过命令运行的原始SQL查询。这些拦截器类分别实现IDbCommandInterceptor和IDbConnectionInterceptor。

对于DbCommandInterceptor，我们使用SqlCommand.CommandText将EXEC sp_set_session_context原始SQL附加在每个通过拦截器的命令之前。

p>

 公共类SessionContextDbCommandInterceptor：IDbCommandInterceptor 
  

对于DbConnectionInterceptor，我们实现Opened方法并针对运行sp_set_session_context SQL的连接执行SqlCommand。

 公共类SessionContextDbConnectionInterceptor：IDbConnectionInterceptor 
 {
 public void Opened（DbConnection connection，DbConnectionInterceptionContext拦截上下文）
 {...} 
  

然后，我们创建了一个DbConfiguration类，在构造函数中添加了拦截器：

 公共类SessionContextConfiguration：DbConfigura 
 {
 public SessionContextConfiguration（）
 {
 AddInterceptor（new SessionContextDbConnectionInterceptor（））; 
 AddInterceptor（new SessionContextDbCommandInterceptor（））; 
} 
} 
  

然后通过以下方式将此DbConfiguration类添加到我们的DbContext类中： DbConfigurationType属性以及我们的web.config：

  [DbConfigurationType（typeof（SessionContextConfiguration））] 
公共类MyContext：DbContext 
 
< entityFramework codeConfigurationType = MyAssembly.SessionContextConfiguration，MyAssembly> 
  

我们像往常一样使用Autofac注入DbContext，并且拦截器会自动添加到DbContext实例中，因为配置类。

I'm following this tutorial in order to use Row Level security in SQL Server via Entity Framework 6 CodeFirst. The tutorial code sample shows how to use IDbConnectionInterceptor and set the current user id in session_context. To retrieve the user id, it uses static accessor method HttpContext.Current.User.Identity.GetUserId() which is coupled with Asp.Net identity and System.Web namespace.

In my multi-tenant web app, I wanted to have the tenantId injected into the DbConnectionInterceptor using Unity (without creating hard-coupling with HttpContext) and set the tenantId in the session_context. I found out that the DbConnectionInterceptor needs to be registered globally (eg. at application startup) and therefore you cannot have Unity create DbConnectionInterceptor instance per request.

I also have 2 DbContexts in my solution representing 2 different databases (Tenant database and a system database) and I only want to apply session_context to the Tenant database only.

It seems that the only option remaining to me is have the tenantId injected into the DbContext isntance via Unity and access the DbContext instance inside the Opened() method of the DbConnectionInterceptor. For this purpose I thought of using the interceptionContext parameter in the Opened() method. interceptionContext has a DbContexts(plural) property. There's no documentation on this so I assumed something like this would work:

public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
{
    var firstDbContext = interceptionContext.DbContexts.FirstOrDefault(d => d is TenantDataContext);
    if (firstDbContext != null)
    {
        var dataContext = firstDbContext as TenantDataContext;
        var tenantId = dataContext.TenantId;

        DbCommand cmd = connection.CreateCommand();
        cmd.CommandText = $"EXEC sp_set_session_context @key=N'TenantId', @value={tenantId};";
        cmd.ExecuteNonQuery();
    }
}

My code checks whether the DbContexts collection contains the TenantDataContext as the first element and executes the sp_set_session_context. But what I'm worried about is whether there's any chance for both DbContexts to be there at the same time? If that was the case, the connection to my other database would also set the session_context which I don't need. I'm wondering why Microsoft has provided this as a collection property rather than a single DbContext property. This property makes you wonder whether the same connection can be used by multiple DbContexts.

Is there anyone who has achieved what I want? Any explanation on this interceptionContext would also be helpful for me.

解决方案

I realize this is an older question, but figured I would post our solution for those looking for one. We are using interceptors to Inject a SQLServer session_context statement into the commands/connections running through EF.

In our case, we had to create Interceptors for DbCommand and DbConnection to handle both EF Linq queries and raw SQL queries that run through Commands. These Interceptor classes implement IDbCommandInterceptor and IDbConnectionInterceptor respectively.

For DbCommandInterceptor, we use the SqlCommand.CommandText to prepend our EXEC sp_set_session_context raw SQL to each command coming through the interceptor.

public class SessionContextDbCommandInterceptor : IDbCommandInterceptor

For DbConnectionInterceptor, we implement the Opened method and execute a SqlCommand against the connection that runs our sp_set_session_context SQL.

public class SessionContextDbConnectionInterceptor : IDbConnectionInterceptor
{
    public void Opened(DbConnection connection, DbConnectionInterceptionContext interceptionContext)
    {...}

We then created a DbConfiguration class that adds the interceptors within the constructor:

public class SessionContextConfiguration : DbConfiguration
{
    public SessionContextConfiguration()
    {
        AddInterceptor(new SessionContextDbConnectionInterceptor());
        AddInterceptor(new SessionContextDbCommandInterceptor());
    }
}

Then add this DbConfiguration class to our DbContext class via the DbConfigurationType Attribute as well as to our web.config:

[DbConfigurationType(typeof(SessionContextConfiguration))]
public class MyContext : DbContext

<entityFramework codeConfigurationType="MyAssembly.SessionContextConfiguration, MyAssembly">

We inject our DbContexts using Autofac as we normally would and the interceptors are automatically added to the DbContext instances because of the Configuration class.

这篇关于实体框架-使用IDbConnectionInterceptor设置session_context的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！