执行Entity Framework时CONTEXT_INFO丢失.SaveChanges（overload） [英] CONTEXT_INFO lost while executing Entity Framework .SaveChanges (overload)
问题描述
解决方案：
您可以找到： here
- 如果操作之前尚未打开
，则对象上下文将打开该连接。如果对象上下文在
操作期间打开连接，那么当操作
完成时，它将始终关闭连接。
- 如果您手动打开连接，对象上下文
将不会关闭它。呼叫关闭或处理将关闭连接。
问题是EF会打开和关闭连接SetUserContext，所以我会松动CONTEXT_INFO。为了保持它，我需要手动打开连接并在SaveChanges之后关闭它
public int SaveChanges（string modifierId）
{
Database.Connection.Open（）;
SetUserContext（modifierId）;
var changes = base.SaveChanges（）;
Database.Connection.Close（）;
返回变化;
}
问题：
系统在数据仓库中工作。数据库必须知道谁修改它并保存审核表中的任何更改。
为了实现这个结果，我主要依靠触发器和过程：
此功能将用户ID保存在CONTEXT_INFO中：
CREATE PROCEDURE [dbo]。[SetUserContext]
@userId NVARCHAR（64）
AS
BEGIN
SET NOCOUNT ON;
DECLARE @context VARBINARY（128）
SET @context = CONVERT（VARBINARY（128），@userId）
SET CONTEXT_INFO @context
END
这个可以在任何地方使用获取userId： p>
CREATE FUNCTION [dbo]。[GetUserContext]（）
RETURNS NVARCHAR（64）
AS
BEGIN
返回转换（NVARCHAR（64），CONTEXT_INFO（））
END
例如在我的触发器中我有：
CREATE TRIGGER UpdateUser
ON [dbo]。[Users]
FOR UPDATE
AS
BEGIN
INSERT INTO [Audit_Users]
SELECT *，dbo.GetUserContext（），GETUTCDATE（），0 FROM inserted
END
GO
CREATE TABLE [dbo]。[Users]（
[Id] NVARCHAR（64）NOT NULL，
[FirstName] NVARCHAR（255）NOT NULL，
[LastName] NVARCHAR（255）NOT NULL，
[BirthDate] DATE NOT NULL，
[Type] INT NOT NULL，
[状态] INT NOT NULL，
[CreatorId] NVARCHAR（64）NOT NULL，
PRIMARY KEY CLUSTERED（[Id] ASC），
CONSTRAINT [FK_Users_ToStatus ] FOREIGN KEY（[Status]）REFERENCES [dbo]。[StatusUsers]（[Id]），
CONSTRAINT [FK_Users_ToCreator] FOREIGN KEY（[CreatorId]）REFERENCES [dbo]。[Users]（[Id]） ，
CONSTRAINT [FK_Users_ToType] FOREIGN KEY（[Type]）REFERENCES [dbo]。[TypeUsers]（[Id]）
）;
CREATE TABLE [dbo]。[Audit_Users]（
[Id] INT IDENTITY（1，1）NOT NULL，
[UserId] NVARCHAR（64）NOT NULL，
[ FirstName] NVARCHAR（255）NOT NULL，
[LastName] NVARCHAR（255）NOT NULL，
[BirthDate] DATE NOT NULL，
[Type] INT NOT NULL，
[状态] INT NOT NULL，
[CreatorId] NVARCHAR（64）NOT NULL，
[ModifierId] NVARCHAR（64）NOT NULL，
[Date] DATETIME NOT NULL，
[删除] INT NOT NULL，
PRIMARY KEY CLUSTERED（[Id] ASC）
）;
当我使用sql请求进行测试时，一切都可以正常工作，所有这一切都起作用。
问题是我需要使用实体框架在我的WCF服务中调用它们。现在这就是麻烦开始了。
I 通过实体设置CONTEXT_INFO，使用重载方法：
public int SaveChanges（string modifierId）
{
SetUserContext（modifierId）;
return base.SaveChanges（）;
}
但是当base.SaveChanges（）;获得调用，我得到：
无法将值NULL插入列ModifierId，表
'dbo.Audit_Users';列不允许为空。 INSERT
失败。该声明已被终止。
哪个建议我丢失了CONTEXT_INFO。我调试（添加一个表并修改setContext过程，该过程用正确的值调用）。
感谢您的帮助我不是数据库专家，可能是一些很简单但我被困在这里..
根据要求：
public partial class Entities：DbContext
{
public Entities（）
：base（name = Entities）
{
}
protected override void OnModelCreating（DbModelBuilder modelBuilder）
{
throw new UnintentionalCodeFirstException（）;
}
public virtual DbSet< Address>地址{get;组; }
public virtual DbSet< Contact>联系人{get;组; }
public virtual DbSet< Email>电子邮件{get;组; }
public virtual DbSet< File>文件{get;组; }
public virtual DbSet< StatusUser> StatusUsers {get;组; }
public virtual DbSet< TypeCommon> TypeCommons {get;组; }
public virtual DbSet< TypeFile> TypeFiles {get;组; }
public virtual DbSet< TypeUser> TypeUsers {get;组; }
public virtual DbSet< User>用户{get;组; }
public virtual DbSet< Workflow>工作流{get;组;
public virtual int SetUserContext（string userId）
{
var userIdParameter = userId！= null？
new ObjectParameter（userId，userId）：
new ObjectParameter（userId，typeof（string））;
return（（IObjectContextAdapter）this）.ObjectContext.ExecuteFunction（SetUserContext，userIdParameter）;
}
}
创建用户：
public UserDto Create（string id，string firstName，string lastName，DateTime birthdate，string type，
string modifierId）
{
var userToReturn = new UserDto
{
Id = id，
FirstName = firstName，
LastName = lastName，
Birthdate = birthdate，
CreatorId = modifierId ，
Status =Created，
Type = type
};
using（var db = ContextFactory.GetEntities（））
{
var user = Mapper.Map< User>（userToReturn）;
using（var transaction = new TransactionScope（））//这创建一个新的事务
{
db.Users.Add（user）;
db.SetUserContext（modifierId）;
if（db.SaveChanges（）== 1）
{
userToReturn = Mapper.Map< UserDto>（user）;
userToReturn.Type = type;
userToReturn.Status =创建;
transaction.Complete（）;
}
}
}
return userToReturn;
}
根据文档 CONTEXT_INFO ，
返回使用SET CONTEXT_INFO语句为当前会话或批次设置的context_info值。
< blockquote>
会话或批处理或多或少对应于.NET托管连接。这在某些方面有助于了解 EF连接管理。
默认的EF行为是开放和关闭数据库连接非常安全，因为.NET连接池使这个效率相当高。在您的情况下，这意味着您的初始存储过程调用发生在后续EF保存操作的不同会话或批处理中。
修复
这很容易解决：您只需要对数据库连接进行明确的控制。您可以通过为您的上下文对象提供构造函数重载，以提供与基础
DbContext类的打开连接，或者在存储过程调用之前手动打开连接。
可以吗？
[你能否建议一个更漂亮的方式这样做？
和
使用EntityFramework的整个重点是避免管理SQL Connections。我觉得某处有些错误。
从底层实现中抽取EF代码通常是不现实的。我不知道它甚至是特别可取的。这种抽象通常更好的是使用库/工作单元。
EF，IMHO的整点是为了避免大量的锅炉代码之间的转换（/）>
（有趣的是，尽管如此，EF 7可能会更容易地保持ORM抽象更纯，甚至提供适合用于自动测试的内存提供商。）
Solution:
As you can find : here
- The object context will open the connection if it is not already open before an operation. If the object context opens the connection during an operation, it will always close the connection when the operation is complete.
- If you manually open the connection, the object context will not close it. Calling Close or Dispose will close the connection.
The problem was that the EF would open and close the connection for SetUserContext, so I would loose the CONTEXT_INFO. In order to keep it I need to open connection manually and close it after the SaveChanges
public int SaveChanges(string modifierId) { Database.Connection.Open(); SetUserContext(modifierId); var changes = base.SaveChanges(); Database.Connection.Close(); return changes; }
Question :
The system work on a datawarehouse. The database has to know who modifies it and saves any change in an Audit table.
To achieve this result I rely mostly on triggers and and procedures :
This function Saves the userId in CONTEXT_INFO :
CREATE PROCEDURE [dbo].[SetUserContext] @userId NVARCHAR (64) AS BEGIN SET NOCOUNT ON; DECLARE @context VARBINARY(128) SET @context = CONVERT(VARBINARY(128), @userId) SET CONTEXT_INFO @context ENDAnd this one can be use anywhere to Get the userId :
CREATE FUNCTION [dbo].[GetUserContext] () RETURNS NVARCHAR (64) AS BEGIN RETURN CONVERT(NVARCHAR (64), CONTEXT_INFO()) ENDFor instance in my trigger I have:
CREATE TRIGGER UpdateUser ON [dbo].[Users] FOR UPDATE AS BEGIN INSERT INTO [Audit_Users] SELECT * , dbo.GetUserContext() , GETUTCDATE() , 0 FROM inserted END GO CREATE TABLE [dbo].[Users] ( [Id] NVARCHAR (64) NOT NULL, [FirstName] NVARCHAR (255) NOT NULL, [LastName] NVARCHAR (255) NOT NULL, [BirthDate] DATE NOT NULL, [Type] INT NOT NULL, [Status] INT NOT NULL, [CreatorId] NVARCHAR (64) NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_Users_ToStatus] FOREIGN KEY ([Status]) REFERENCES [dbo].[StatusUsers] ([Id]), CONSTRAINT [FK_Users_ToCreator] FOREIGN KEY ([CreatorId]) REFERENCES [dbo].[Users] ([Id]), CONSTRAINT [FK_Users_ToType] FOREIGN KEY ([Type]) REFERENCES [dbo].[TypeUsers] ([Id]) ); CREATE TABLE [dbo].[Audit_Users] ( [Id] INT IDENTITY (1, 1) NOT NULL, [UserId] NVARCHAR (64) NOT NULL, [FirstName] NVARCHAR (255) NOT NULL, [LastName] NVARCHAR (255) NOT NULL, [BirthDate] DATE NOT NULL, [Type] INT NOT NULL, [Status] INT NOT NULL, [CreatorId] NVARCHAR (64) NOT NULL, [ModifierId] NVARCHAR (64) NOT NULL, [Date] DATETIME NOT NULL, [Deleted] INT NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC) );Everything seemed to work fine, when I test in with sql request and all it all works. Problem is that I need to call them in my WCF service using Entity Framework. And now that's where trouble begins. I set CONTEXT_INFO through entity with an overload method:
public int SaveChanges(string modifierId) { SetUserContext(modifierId); return base.SaveChanges(); }But when the base.SaveChanges(); Gets called, I get:
Cannot insert the value NULL into column 'ModifierId', table 'dbo.Audit_Users'; column does not allow nulls. INSERT fails. The statement has been terminated.
Which suggest that I lost CONTEXT_INFO. I debugged (Adding a table and modifying the setContext procedure and the procedure is called with the proper values).
Thanks for your help I'm no database expert it might be something pretty simple but I'm stuck here..
As requested:
public partial class Entities : DbContext { public Entities() : base("name=Entities") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { throw new UnintentionalCodeFirstException(); } public virtual DbSet<Address> Addresses { get; set; } public virtual DbSet<Contact> Contacts { get; set; } public virtual DbSet<Email> Emails { get; set; } public virtual DbSet<File> Files { get; set; } public virtual DbSet<StatusUser> StatusUsers { get; set; } public virtual DbSet<TypeCommon> TypeCommons { get; set; } public virtual DbSet<TypeFile> TypeFiles { get; set; } public virtual DbSet<TypeUser> TypeUsers { get; set; } public virtual DbSet<User> Users { get; set; } public virtual DbSet<Workflow> Workflows { get; set; } public virtual int SetUserContext(string userId) { var userIdParameter = userId != null ? new ObjectParameter("userId", userId) : new ObjectParameter("userId", typeof(string)); return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("SetUserContext", userIdParameter); } }Create User:
public UserDto Create(string id, string firstName, string lastName, DateTime birthdate, string type, string modifierId) { var userToReturn = new UserDto { Id = id, FirstName = firstName, LastName = lastName, Birthdate = birthdate, CreatorId = modifierId, Status = "Created", Type = type }; using (var db = ContextFactory.GetEntities()) { var user = Mapper.Map<User>(userToReturn); using (var transaction = new TransactionScope()) // this creates a new transaction { db.Users.Add(user); db.SetUserContext(modifierId); if (db.SaveChanges() == 1) { userToReturn = Mapper.Map<UserDto>(user); userToReturn.Type = type; userToReturn.Status = "Created"; transaction.Complete(); } } } return userToReturn; }
解决方案According to the documentation
CONTEXT_INFO,Returns the context_info value that was set for the current session or batch by using the SET CONTEXT_INFO statement.
The "session or batch" more or less corresponds to the .NET managed connection. This is where understanding a bit about EF connection management helps somewhat.
The default EF behavior is to open and close the database connection quite freely—safe in the knowledge that .NET connection pooling makes this reasonably efficient. In your case, this will mean that your initial stored procedure call happens in a different "session or batch" to the subsequent EF save operation.
The Fix
This is quite easy to fix: you just need to take explicit control over the database connection. You can do this either by providing a constructor overload for your context object that supplies an open connection to the base
DbContextclass, or by opening the connection manually before your stored procedure call.Is This OK?
[Could you] suggest a prettier way to do it?
and
The whole point of using EntityFramework is to avoid having to manage SQL Connections. I feel something is wrong somewhere.
It's usually not realistic to abstract EF code away from the underlying implementation. I'm not sure it's even particularly desirable. This abstraction is usually better with a repository/unit of work layer.
The "whole point" of EF, IMHO, is to avoid lots of boiler-plate code translating between raw data from the database and .NET object representations of that data.
(Interestingly, though, EF 7 will probably make it easier to keep the ORM abstraction "purer," even providing an in-memory provider suitable for use in automated tests.)
这篇关于执行Entity Framework时CONTEXT_INFO丢失.SaveChanges（overload）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
