title: Entity Framework 5 Identity Insert
body: <p>I am trying to insert a record into a table with an identity primary key.  Sometimes, I need to set the pk. This can for instance be useful when creating known test data to make reliable integration tests or to debug a production issue in an isolated environment.</p>
  
  <p>Other posts say to execute a sql statement:</p>
  
  <pre><code>private void IdentityInsertOK()
      {
          var sql = "set identity_insert  ConfigSettings on " +
                     "delete from ConfigSettings where id =2 " +
                    "insert into ConfigSettings (Id,Name, value) values (2,'test ','testval')  " +
                    "set identity_insert  ConfigSettings off ";
          using (var Db = SettingsHelper.CreateContext(ConnectionType.Syrius))
          {
              Db.Database.ExecuteSqlCommand(sql);
              Db.SaveChanges();
          }
  
      }
  </code></pre>
  
  <p>While the SQL insert statement works, it defeats the propose/benefits of the Entity Framework. (especially the prevention of SQL injection).</p>
  
  <p>I have tried the following, but it fails on <code>context.SaveChanges</code>:</p>
  
  <pre><code>private static void InsertEmployee(EmployeeModel employee)
  {
          var emp = new Employee //The database employee record 
              {
                  EmployeeId = emp.EmployeeId,
                  FirstName = emp.FirstName,
                  ...
              };
  
          using (var context = new EmployeeEntities())
          {
              try
              {
                  context.Database.Connection.Open();
  
                  using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew))
                  {
                      context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employee ON");
                      context.Employees.Add(emp);
                      context.SaveChanges();
                      context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employee OFF");
                      scope.Complete();
                  }
              }
              finally
              {
                  context.Database.Connection.Close();
              }
          }
      }
  </code></pre>
  
  <p>Getting DB error: </p>
  
  <blockquote>
    <p>Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF.</p>
  </blockquote>
  
  <p>(SQL Profiler shows that each 'action' is its own 'batch') </p>
  
  <p>(Another posting stays to use <code>ExecuteStoreCommand</code> to turn on &amp; off the identity insert, but that appears to be gone in EF5.)</p>
  
  <p>I have turned off connection pooling in the connection string, but still no joy.  Any ideas how to make this work?  Or is there another method - best practice - to do this?</p>