CONTEXT_INFO lost while executing Entity Framework .SaveChanges (overload)

Question

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
END

And 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())
END

For 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;
}

Answer 1

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 DbContext class, 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.)

Answer 2

The reason why your Context_Info() is null is because stored procedures are executed immediately after you invoke them. They are not invoked when you do dataContext.SaveChanges(). What you want to do is invoke the stored procedure within the same transaction as the dataContext.SaveChanges(). In order to do so, this is how your code should really look like.

public partial class MyDbContext : DbContext 
{
    //...

    public virtual int SetUserContext(string modifierId)
    {
        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("SetUserContext", modifierId);  
    }
}

public class UserService 
{
    private MyDbContext m_dataContext;

    public UserService(MyDbContext dataContext)
    {
        m_dataContext = dataContext;
    {

    public User CreateUser(string firstName, string lastName, DateTime birthDate, int modifiedId) // list other parameters here
    {
        using (var transaction = new TransactionScope()) // this creates a new transaction
        {
            m_dataContext.Users.Add(new User()
            {
                //...
            });

            // instead of passing modified id into save changes, you can just call your stored procedure here
            m_dataContext.SetUserContext(modifiedId);

            // and then call the regular save changes
            m_dataContext.SaveChanges();

            transaction.Complete(); // this commits the transaction
        }
    }
}

NOTE The architecture of the end solution as presented here is not that good at the moment. I would recommend implementing the Repository pattern instead of letting the Service have access to the data context.