In Entity Framework, getting the value of an identity column after inserting

Question

I'm using EF4. I want to insert a new MyObject into the database. MyObject has two fields:

Id: int (Identity) and Name: string

As I've seen in documentation Entity Framework is supposed to set MyObject.Id to the value generated by database after the call to SaveChanges() but in my case that doesn't happen.

using (var context = new MyEntities())
{
    var myObject = MyObjects.CreateMyObject(0, "something"); // The first parameter is identity "Id"
    context.MyObjects.AddObject(myObject);
    context.SaveChanges();
    return myObject.Id; // The returned value is 0
}

UPDATE:

This happens in one of my entities and others work fine. By the way, I checked and the DB column is identity and StoreGeneratedPattern is set to Identity. Here is the SSDL. I don't see any difference. The first one isn't working right:

    <EntityType Name="OrgUnit">
      <Key>
        <PropertyRef Name="Srl" />
      </Key>
      <Property Name="Srl" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="TypeId" Type="smallint" Nullable="false" />
      <Property Name="Name" Type="varchar" Nullable="false" MaxLength="80" />      
    </EntityType>

    <EntityType Name="OrgType">
      <Key>
        <PropertyRef Name="Srl" />
      </Key>
      <Property Name="Srl" Type="smallint" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="Title" Type="varchar" Nullable="false" MaxLength="120" />
      <Property Name="Options" Type="int" Nullable="false" />
    </EntityType>

The update is done successfully in the database and the identity is generated but the entity object is not updated with the new identity.

Aluminium

In that case, you EF model is probably not up to date - EF should automagically get your new ID from the database. Try refreshing your EF model – Jeanett

Answer 1

wow! that was a nightmare but at last I solved it, although I didn't understand what the problem was. Maybe this helps someone with the same problem.

1. Generate the script for creating the table and its data.
2. Drop the table.
3. Run the script.

Aluminium

Answer 2

In that case, you EF model is probably not up to date - EF should automagically get your new ID from the database. Try refreshing your EF model.

Your identity column's properties should look like this in your EDMX model:

Jeanett

Answer 3

If you're using Oracle Entity Framework 4 Provider, like I do, from ODP.NET, there is a bug in Designer. Just selecting the Identity value in drop down box will not do. It will annotate the conceptual property in conceptual model with

annotation:StoreGeneratedPattern="Identity"

like in

<Property Type="Int32" Name="Id" Nullable="false" cg:SetterAccess="Private" annotation:StoreGeneratedPattern="Identity" />

But, it will fail to do the same for Storage Model, ie. you need to do it manually. Find the Property (in my case ID) in EntityType of interest and add StoreGeneratedPattern="Identity".

    <EntityType Name="PROBLEMI">
      <Key>
        <PropertyRef Name="ID" />
      </Key>
      <Property Name="ID" Type="number" Nullable="false" Precision="10" StoreGeneratedPattern="Identity" />

I'm not aware of the same bug in SQL EF provider 'cos I didn't use it.

Dorolisa

This is the correct answer. I have been looking over internet and could not find an explanation like this. Thank you. By the way, this situaiton is supposed to be a result of visual studio 2010 bug, and some say that service pack 1 for visual studio 2010 solves the problem. I am gonna give this update a try now... – Sumerian

Answer 4

This should "just work." Make sure the DB column actually is IDENTITY, and that StoreGeneratedPattern is set to Identity in EDMX.

Stimson

OK. What's different about those two. Diff the SSDL in the EDMX. Diff the DDL. Something is not the same. – Stimson

I added my SSDL to the question and it seems the same for both entities. – Aluminium

It's not the same. The Type is different. I wonder if that's significant? – Stimson

Answer 5

If you are using Linq To Entities, and get this error even if you followed the advices of marc_s (that are really good), you should look at your entites directly in the edmx (xml view) and check if they have the following attribute :

    <EntityType Name="MyEntity">
      <Key>
        <PropertyRef Name="pk" />
      </Key>
      <Property Name="pk" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
      <Property Name="value" Type="float" Nullable="false" />
    </EntityType>

The StoreGeneratedPattern="Identity" is also required.

Heredia

Answer 6

Try using refresh method after Save Changes, it has been documented in MSDN

"To ensure that objects on the client have been updated by data source-side logic, you can call the Refresh method with the StoreWins value after you call SaveChanges."

http://msdn.microsoft.com/en-us/library/bb336792.aspx

Though i feel what @Craig has suggested might also work.

Donelu

That isn't necessary for properties which have StoreGeneratedPattern set. You need to do it, e.g., for properties which may be updated for a trigger. – Stimson

Answer 7

I ran into this today. The difference though was I was using an insert function, where the above person doesn't specify that. What I had to do was make my insert function stored procedure return SCOPE_IDENTITY() and use a result binding for the id returned.

Fixed my issue.

Shaeshaef