20

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 System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Objects.ObjectContext.SaveChanges() at DataMigration.Program.CopyInstructors() in C:\Projects\DataMigration\Program.cs:line 52

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
dcompiled
  • 4,762
  • 6
  • 33
  • 36
  • 4
    I'm guessing that if you just leave the autonumber field blank, the database will assign one for you when you commit. – Robert Harvey Jun 10 '10 at 05:09
  • 1
    Yes it works, it adds the records and will assign primary keys the problem is that I shouldn't be getting an exception for such a typical scenario. I'm sure there is a 'correct' way of doing this within the framework, just not sure how. – dcompiled Jun 10 '10 at 05:40
  • 1
    Well now the exception stopped being thrown after I made some miscellaneous changes to the table and updated the model. Not sure what made the difference. The only changes I made was to add some default values for columns and set those columns to not null. Not convinced the changes are related to the exception. – dcompiled Jun 10 '10 at 06:25
  • 1
    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. – pavan kumar Jan 28 '21 at 18:24

4 Answers4

21

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

Devart
  • 119,203
  • 23
  • 166
  • 186
  • 7
    Note that you can't just set this property in the designer, you have to edit the SSDL section by hand. I read past this answer and thought: "I've already done that.", and I kept looking, I could have save hours by noticing the *SSDL* This is a know bug in the designer. http://geeksharp.com/2010/05/27/ef4-bug-in-storegeneratedpattern-ssdl/ and http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/404d3017-01b7-4129-8e05-f4aa48f15f08 – DanO Jul 21 '10 at 21:57
  • you may want to weigh in on this: http://meta.stackexchange.com/questions/82509/should-accounts-used-by-multiple-users-be-allowed/82519#82519 – smartcaveman Mar 10 '11 at 23:46
  • we are starting a new MVC 4 project using VS2012, EF 5 and ODP.NET for Oracle 11 and we are experiencing exactly the same problem. Everytime we "generate database from model", the ssdl part on edmx file loses the "storedGeneratedPatter=Identity" attribute. Does anybody know any hotfix for VS2012 and EF 5 supporting Oracle too? – as-cii Dec 11 '12 at 12:45
  • 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. – pavan kumar Jan 28 '21 at 18:25
16

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.

cepriego
  • 873
  • 1
  • 6
  • 10
  • 3
    I think where I went wrong is in that I initially created the model from the database and forgot to set the autonumber field. LaterI edited my database and set this property followed by the "update model from database" command within visual studio 2010. The update feature seems to add new properties but not exactly sure if it 'fixes' existing modified properties correctly. By deleting my model and recreating from scratch, the autonumber feature worked as expected. However had I known better, I would have tried your idea. – dcompiled Jun 14 '10 at 19:02
  • 1
    Currently (but hopefully soon!) I'm not using EF4, however I read something in this page that talks about this: http://geeksharp.com/2010/05/27/ef4-bug-in-storegeneratedpattern-ssdl/ – cepriego Jun 15 '10 at 09:08
  • 3
    Microsoft provides a hotfix here for VS2010SP1. http://connect.microsoft.com/VisualStudio/Downloads/DownloadDetails.aspx?DownloadID=37957 It seems to work for me. – kimsk Jul 06 '12 at 20:24
  • 1
    @kimsk The link doesn't work anymore. What do I need to download? – AlexGH Aug 02 '19 at 20:06
  • 1
    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. – pavan kumar Jan 28 '21 at 18:26
7

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:

yu yang Jian
  • 6,680
  • 7
  • 55
  • 80
1

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.

pavan kumar
  • 408
  • 5
  • 19