title: CONTEXT_INFO lost while executing Entity Framework .SaveChanges (overload)
body: <p><strong>Solution:</strong> </p>
  
  <p>As you can find : <a href="https://msdn.microsoft.com/en-us/library/bb738582(v=vs.90).aspx" rel="noreferrer">here</a></p>
  
  <blockquote>
    <ul>
    <li>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. </li>
    <li>If you manually open the connection, the object context
    will not close it. Calling Close or Dispose will close the connection.</li>
    </ul>
  </blockquote>
  
  <p>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</p>
  
  <pre><code>public int SaveChanges(string modifierId)
  {
          Database.Connection.Open();
          SetUserContext(modifierId);
          var changes = base.SaveChanges();
          Database.Connection.Close();
          return changes;            
   }
  </code></pre>
  
  <hr>
  
  <p><strong>Question :</strong></p>
  
  <p>The system work on a datawarehouse. The database has to know who modifies it and saves any change in an Audit table.</p>
  
  <p>To achieve this result I rely mostly on triggers and and procedures : </p>
  
  <p>This function <strong>Saves the userId in CONTEXT_INFO :</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>And this one can be use anywhere to <strong>Get the userId :</strong></p>
  
  <pre><code>CREATE FUNCTION [dbo].[GetUserContext] ()
  RETURNS NVARCHAR (64)
  AS
  BEGIN
      RETURN CONVERT(NVARCHAR (64), CONTEXT_INFO())
  END
  </code></pre>
  
  <p>For instance in my trigger I have:</p>
  
  <pre><code>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)
  );
  </code></pre>
  
  <p>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 <strong>set CONTEXT_INFO through entity</strong> with an overload method:</p>
  
  <pre><code> public int SaveChanges(string modifierId)
      {
          SetUserContext(modifierId);
          return base.SaveChanges();
      }
  </code></pre>
  
  <p>But when the base.SaveChanges(); Gets called, I get: </p>
  
  <blockquote>
    <p>Cannot insert the value NULL into column 'ModifierId', table
    'dbo.Audit_Users'; column does not allow nulls. INSERT
    fails. The statement has been terminated.</p>
  </blockquote>
  
  <p>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).</p>
  
  <p>Thanks for your help I'm no database expert it might be something pretty simple but I'm stuck here.. </p>
  
  <p>As requested:</p>
  
  <pre><code> public partial class Entities : DbContext
      {
          public Entities()
              : base("name=Entities")
          {
          }
  
          protected override void OnModelCreating(DbModelBuilder modelBuilder)
          {
              throw new UnintentionalCodeFirstException();
          }
  
          public virtual DbSet&lt;Address&gt; Addresses { get; set; }
          public virtual DbSet&lt;Contact&gt; Contacts { get; set; }
          public virtual DbSet&lt;Email&gt; Emails { get; set; }
          public virtual DbSet&lt;File&gt; Files { get; set; }
          public virtual DbSet&lt;StatusUser&gt; StatusUsers { get; set; }
          public virtual DbSet&lt;TypeCommon&gt; TypeCommons { get; set; }
          public virtual DbSet&lt;TypeFile&gt; TypeFiles { get; set; }
          public virtual DbSet&lt;TypeUser&gt; TypeUsers { get; set; }
          public virtual DbSet&lt;User&gt; Users { get; set; }
          public virtual DbSet&lt;Workflow&gt; 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);
          }
      }
  </code></pre>
  
  <p>Create User:</p>
  
  <pre><code>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&lt;User&gt;(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&lt;UserDto&gt;(user);
                  userToReturn.Type = type;
                  userToReturn.Status = "Created";
                  transaction.Complete();
              }
          }
      }
      return userToReturn;
  }
  </code></pre>