当与Linq一起使用DbContext时，执行AS [英] EXECUTE AS when using a DbContext with Linq
问题描述
一旦我确定了租户，我想切换通过执行如下所示的SQL语句到适当的用户上下文：
EXECUTE AS User ='Tenant1'WITH NO REVERT
当我使用DbContext的Database属性的ExecuteSqlCommand执行此命令时，一切似乎都正常工作。当我以后使用Linq修改模型并调用方法
myDbContext.SaveChanges（）;
我得到一系列例外：
在提供者
连接上启动事务时发生错误。查看内部例外情况。
其中有一个例外：
当前命令发生严重错误。结果（如果有的话）应该被丢弃。
可以这样改变用户的执行上下文，如果是这样，最好的方式是什么？
我需要的大部分答案都是在这里找到的： p rel rel rel rel =nofollow> http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/
{pre>
public partial class MyDBContext
{
public MyDBContext（）：base（）{}
private MyDBContext（DbConnection connection，DbCompiledModel model） base（connection，model，contextOwnsConnection：false）{}
private static ConcurrentDictionary< Tuple< string，string>，DbCompiledModel> modelCache
= new ConcurrentDictionary< Tuple< string，string>，DbCompiledModel>（）;
public static MyDBContext Create（string tenantSchema，DbConnection connection）
{
var compiledModel = modelCache.GetOrAdd
（
Tuple.Create（connection.ConnectionString ，tenantSchema），
t =>
{
var builder = new DbModelBuilder（）;
builder.Conventions.Remove& IncludeMetadataConvention>（）;
builder.Entity< ;位置>（）。ToTable（Locations，tenantSchema）;
builder.Entity< User>（）。ToTable（Users，tenantSchema）;
var model = builder。 Build（connection）;
return model.Compile（）;
}
）;
var context = new FmsDBContext（connection，compiledModel）;
if（！string.IsNullOrEmpty（tenantSchema）&！tenantSchema.Equals（dbo，StringComparison.OrdinalIgnoreCase））
{
var objectContext =（（IObjectContextAdapter ）context）.ObjectContext;
objectContext.Connection.Open（）;
var currentUser = objectContext.ExecuteStoreQuery< UserContext>（SELECT CURRENT_USER AS Name，null）.FirstOrDefault（）;
if（currentUser.Name.Equals（tenantSchema，StringComparison.OrdinalIgnoreCase））
{
var executeAs = string.Format（REVERT; EXECUTE AS User ='{0}';， tenantSchema）;
objectContext.ExecuteStoreCommand（executeAs）;
}
}
返回上下文;
}
}
然后，您可以访问Schema的信息： / p>
using（var db = MyDBContext.Create（schemaName，dbConn））
{
// ..
}
然而，这实际上绕过数据库用户。我仍然在研究如何使用数据库用户的上下文，而不是仅仅指定模式名称。
更新：
我终于解决了最后一个障碍。关键是以下代码：
if（！string.IsNullOrEmpty（tenantSchema）&！tenantSchema.Equals（ dbo，StringComparison.OrdinalIgnoreCase））
{
var objectContext =（（IObjectContextAdapter）context）.ObjectContext;
objectContext.Connection.Open（）;
var currentUser = objectContext.ExecuteStoreQuery< UserContext>（SELECT CURRENT_USER AS Name，null）.FirstOrDefault（）;
if（currentUser.Name.Equals（tenantSchema，StringComparison.OrdinalIgnoreCase））
{
var executeAs = string.Format（REVERT; EXECUTE AS User ='{0}';， tenantSchema）;
objectContext.ExecuteStoreCommand（executeAs）;
}
}
EXECUTE AS命令在它之前的连接上发出用于执行后续的linq到实体命令。只要连接保持打开状态，用户的上下文保持原样。在我的数据库中，租户的模式名称和用户名是相同的。
多次更改用户的执行上下文将导致错误，因此使用快速查询来确定当前用户上下文。需要一个小实体类来使用连接来检索信息：
私有类UserContext
{
public string Name {get;组; }
}
I'm designing a multi-tenant database where each tenant is given a corresponding database user. The user is assigned access rights to the schema associated with the tenant and specific rights to objects in the dbo schema.
Once I've identified the tenant, I want to switch to the appropriate user context by executing a SQL statement like the following:
EXECUTE AS User = 'Tenant1' WITH NO REVERT
When I execute this command using the ExecuteSqlCommand of the DbContext's Database property, everything seems to work correctly. When I later make changes to the model using Linq and call the method
myDbContext.SaveChanges();
I get a series of exceptions:
An error occurred while starting a transaction on the provider connection. See the inner exception for details.
with an inner exception of:
A severe error occurred on the current command. The results, if any, should be discarded.
Is it possible to change the user's execution context in this way and, if so, what is the best way to do so?
Most of the answer I needed was found here:
http://romiller.com/2011/05/23/ef-4-1-multi-tenant-with-code-first/
public partial class MyDBContext
{
public MyDBContext() : base() { }
private MyDBContext(DbConnection connection, DbCompiledModel model) : base(connection, model, contextOwnsConnection: false) { }
private static ConcurrentDictionary<Tuple<string, string>, DbCompiledModel> modelCache
= new ConcurrentDictionary<Tuple<string, string>, DbCompiledModel>();
public static MyDBContext Create(string tenantSchema, DbConnection connection)
{
var compiledModel = modelCache.GetOrAdd
(
Tuple.Create(connection.ConnectionString, tenantSchema),
t =>
{
var builder = new DbModelBuilder();
builder.Conventions.Remove<IncludeMetadataConvention>();
builder.Entity<Location>().ToTable("Locations", tenantSchema);
builder.Entity<User>().ToTable("Users", tenantSchema);
var model = builder.Build(connection);
return model.Compile();
}
);
var context = new FmsDBContext(connection, compiledModel);
if( !string.IsNullOrEmpty( tenantSchema ) && !tenantSchema.Equals( "dbo", StringComparison.OrdinalIgnoreCase ) )
{
var objectContext = ( (IObjectContextAdapter)context ).ObjectContext;
objectContext.Connection.Open();
var currentUser = objectContext.ExecuteStoreQuery<UserContext>( "SELECT CURRENT_USER AS Name", null ).FirstOrDefault();
if( currentUser.Name.Equals( tenantSchema, StringComparison.OrdinalIgnoreCase ) )
{
var executeAs = string.Format( "REVERT; EXECUTE AS User = '{0}';", tenantSchema );
objectContext.ExecuteStoreCommand( executeAs );
}
}
return context;
}
}
Then you can access the Schema's information like this:
using (var db = MyDBContext.Create( schemaName, dbConn ))
{
// ...
}
This bypasses actually using the database user however. I'm still working on how to use the database user's context instead of just specifying the schema name.
Update:
I finally solved the last hurdle here. The key was the following code:
if( !string.IsNullOrEmpty( tenantSchema ) && !tenantSchema.Equals( "dbo", StringComparison.OrdinalIgnoreCase ) )
{
var objectContext = ( (IObjectContextAdapter)context ).ObjectContext;
objectContext.Connection.Open();
var currentUser = objectContext.ExecuteStoreQuery<UserContext>( "SELECT CURRENT_USER AS Name", null ).FirstOrDefault();
if( currentUser.Name.Equals( tenantSchema, StringComparison.OrdinalIgnoreCase ) )
{
var executeAs = string.Format( "REVERT; EXECUTE AS User = '{0}';", tenantSchema );
objectContext.ExecuteStoreCommand( executeAs );
}
}
The EXECUTE AS command is issued on the connection before it is used to perform later linq to entity commands. As long as the connection remains open, the user's context remains in place. In my database the schema name and username for a tenant are the same.
Changing the user's execution context multiple times will cause errors, so a quick query is used to determine the current user context. A small entity class is needed to retrieve the information using the connection:
private class UserContext
{
public string Name { get; set; }
}
这篇关于当与Linq一起使用DbContext时，执行AS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
