How to obtain the identity of an entity after calling SaveChanges() when the entity is mapped to stored procedures

Question

We are using Entity Framework 4.0 and we have an entity that is mapped to stored procedures provided by our DBA. The Insert, Update, and Delete functions in the mapping details all have their own stored procedures.

When using entities that are mapped to tables I am able to add a new entity, call dataContext.SaveChanges(); and then the new entity I instantiated automatically has its ID property populated with the value from the identity column in the database.

How can this be accomplished when the entity is mapped to stored procedures? Does the INSERT stored procedure have to do something special and/or do I have to do something special on dataContext.SaveChanges();?

Example

Traditional way

var newCustomer = new Customer
{
    Name = "Fred",
    Age = 24
};

// newCustomer.Id is null

dataContext.Customers.Add(newCustomer);
dataContext.SaveChanges()

// newCustomer.Id is what database identity column was set to.

Mapped to stored procedures.

var newCustomer = new Customer
{
    Name = "Fred",
    Age = 24
};

// newCustomer.Id is null

dataContext.Customers.Add(newCustomer);
dataContext.SaveChanges()

// newCustomer.Id is null

Answer 1

If you are using Identity column in database make sure that your stored procedure contains:

SELECT Scope_Identity() AS Id

after calling INSERT

Also make sure that PK in your entity mode is correctly configured with StoreGeneratedPattern set to Identity (should be automatically if you used Update from database)

Answer 2

I believe your DB needs to use @@identity or insert with a NewID() and return the Identity/NewID value back to you via the stored procedure. You technically could select from the database for the record you inserted, but that is a very questionable way to do it as; you don't know if the records was inserted (unless the SP failed at .Net), you may not know if duplicated records exists, or even if the data was changed after the insert but before the select. When in doubt I always highly recommend talking to your DBA about the best approach to your specific needs based your DBAs design.

Updates:

If he returns you the PK value, you should be able to do a standard select from the table to populate the entity like from e in context.entities where e.pkcolumn = spkeyreturned select e.

If he returns you ALL the data back, and can guarantee the data won't change, you might be able to create a new entity, populate it will all the data and use the Attach method. I personally wouldn't do that, but it is an option. Attaching and Detaching Object in Entity Framework.