Autonumber with Entity Framework

I want to loop through a collection of objects and add them all to a table. The destination table has an auto-increment field. If I add a single object there is no problem. If I add two objects both with the primary key of zero, the entity framework fails. I can manually specify primary keys but the whole point of trying the EF was to make life easier not more complicated. Here is the code and the exception received follows.

foreach (Contact contact in contacts)
{               
    Instructor instructor = InstructorFromContact(contact);             
    context.AddToInstructors(instructor);               
}

try
{                   
    context.SaveChanges();                  
}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}

The error is:

System.InvalidOperationException: The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges. at Syste开发者_运维百科m.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)

at System.Data.Objects.ObjectContext.SaveChanges() at DataMigration.Program.CopyInstructors() in C:\Projects\DataMigration\Program.cs:line 52

Set the StoreGeneratedPattern attribute to "Identity" in your SSDL for the autoincrement field. It should help.

This happens because despite the auto-generated value of the column was created in the Database the EF never knew about it.

So, in order to inform EF that the DB will handle the generated value you have to to open your edmx file (I always use the XML editor of VS to do this) and in the store schema definition language (SSDL) area, add the attribute StoreGeneratedPattern="Identity" to the column that needs the generated pattern. In this way EF reads the value generated in the DB and stores it in memory cache.

Your entity type definition will look more or less like this:

 <EntityType Name="INVOICE">
          <Key>
            <PropertyRef Name="CODE" />
          </Key>
          <Property Name="CODE" Type="varchar" Nullable="false"
              MaxLength="10" StoreGeneratedPattern="Identity"/>                 
 </EntityType>

Be aware that if you happen to update your model all these changes will be lost and you'll have to repeat all the entire process.

This works for EF 1.0, I'm not sure if in EF4 all these issues are already fixed.

I'm using EF6, to set the StoreGeneratedPattern, you can also try open EDMX file in Visual Studio, right click on the Data Column in table and select Properties,

Then you can set it from None to Identity in Properties Window:

May sound silly but in our case there was an unnecessary select statement in a trigger that was making the trigger return data and the SaveChanges() actually took quite some time to execute and eventually thrown the above error.