CODEWITHSUNDEEP
entity-frameworkasp.net-mvc-3
Volatil3
Volatil3

Reputation: 14978

How to get Last Inserted ID if using Entity Framework

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?

Upvotes: 8

Views: 20297

Answers (2)

Justin Maynard
Justin Maynard

Reputation: 11

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?

Upvotes: 0

marc_s
marc_s

Reputation: 754268

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!

Upvotes: 21

Related Questions