Entity Framework 5 Identity Insert

Question

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.

Other posts say to execute a sql statement:

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();
        }

    }

While the SQL insert statement works, it defeats the propose/benefits of the Entity Framework. (especially the prevention of SQL injection).

I have tried the following, but it fails on context.SaveChanges:

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();
            }
        }
    }

Getting DB error:

Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF.

(SQL Profiler shows that each 'action' is its own 'batch')

(Another posting stays to use ExecuteStoreCommand to turn on & off the identity insert, but that appears to be gone in EF5.)

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?

Answer 1

This problem occurs, because you need to tell EF that the Key column (Id) should not be database generated.

[DatabaseGenerated(DatabaseGenerationOption.None)]

or through the modelBuilder:

Property(obj => obj.Id)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
    .HasColumnName("Id");