How to get Last Inserted ID if using Entity Framew

问题:

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?

回答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!

回答2:

What if you needed to insert into a table without triggering validation on the models used to insert into the table? IE in the DB a field is not required and an insert of null would work, but in the EF Model it is a required field?