How to get Last Inserted ID if using Entity Framework

Question

I am using EF to add record. I want to get the last Inserted ID. Following is my Code:

string query = "INSERT INTO MyTable(PONumber, Status, UpdatedBy, UpdatedOn, CreatedOn) VALUES(@PO_NUMBER, '0', @STAFF, GETDATE(), GETDATE())";

parameterList = new List<object>();
parameterList.Add(new SqlParameter("@PO_NUMBER", poNumber));
parameterList.Add(new SqlParameter("@STAFF",staff));

parameters = parameterList.ToArray();

result = db.Database.ExecuteSqlCommand(query, parameters);

query = "SELECT NewID = SCOPE_IDENTITY();";

var id = db.Lists.SqlQuery(query);

How do I iterate record from var id?

Answer 1

If you're using EF, the whole point is that you don't have to fiddle around with raw SQL. Instead, you use the object classes generated by EF corresponding to your database tables.

So in your case, I would much rather do something like this:

// create the EF context
using(YourEFContext ctx = new YourEFContext())
{
     // create a new "MyTable" class
     MyTable newEntity = new MyTable();

     // set its properties
     newEntity.PoNumber = poNumber;
     newEntity.Status = 0;
     newEntity.CreatedOn = DateTime.Now;
     newEntity.UpdatedOn = DateTime.Now;
     newEntity.UpdatedBy = staff;

     // add new entity to EF context
     ctx.MyTable.Add(newEntity);

     // save changes to database
     ctx.SaveChanges();

     // read out your newly set IDENTITY value 
     int newIdentityValue = newEntity.ID;
}

Clean object-oriented code - no messy SQL needed at all!