none
Performing an IDENTITY_INSERT using EF5 RRS feed

  • Question

  • I am trying to insert a record into a table with an identity primary key.  Doing the following only adds a new record with a new ID.  SQL Profiler shows that each 'action' is its own 'batch', so doing the context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employee ON"); does not help.

    What can I do to solve my issue? 

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

    Friday, July 12, 2013 2:35 PM

Answers

  • If the following code is creating a new record to be inserted into the database :

     var emp = new Employee //The database employee record 
               {
                   EmployeeId = emp.EmployeeId,
                   FirstName = emp.FirstName,
                   ...
               };

    and EmployeeId is defined as a primary with Identity set on then this line of code should not in their.

                   EmployeeId = emp.EmployeeId,

    This is because Entity Framework will request that the record be inserted and SQL server will insert and send the value of the primary key back to Entity Framework and EF will update the value EmployeeId. If you attempt to place a value into an Identity it should be throwing an exception.

    Now you say that the above code, "The database employee record", and you are instantiating a new record, also missing () at the end of the method call, all the emp values are going to be set to default values, so I am not sure what you are attempting to accomplish here.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Friday, July 12, 2013 7:45 PM