sql server - How to get a "New Record Id" after inserting a record using ExecuteStoreCommand("INSERT -

i using mvc3, asp.net4.5, c#, ef5, sql server 2008 r2.

i want understand best approach "new record id" once record has been inserted, given the pk int32 identity column.

my current code is:

db.executestorecommand("insert table1(col1,col2 ) select col1,col2 table1 id =   {0}", myoldrecordid); command = @"select ident_current({0}) current_identity;"; mynewrecordid = (int)db.executestorequery<decimal>(command, "table1").first(); 

while above works, concern else insert record same table @ same time, retrieved newrecordid = correct value + 1.

what best way retrieve new newrecordid, once "insert" has been issued via executestorecommand?

btw, need use raw dml, number of reasons, rather pocos via ef.

thanks in advance.

edit

the code change per understanding of advice given:

using(transactionscope tran = new transactionscope())  {    db.executestorecommand("insert table1(col1,col2 ) select col1,col2 table1 id =   {0}", myoldrecordid);    command = @"select scope_identity ({0}) current_identity;";    mynewrecordid = (int)db.executestorequery<decimal>(command, "table1").first();     tran.complete(); } 

i think still need change this, make "db.executestorecommand" , "db.executestorequery" transaction aware.

edit2

after more research, have discovered this:

db.connection.open(); //db = ef dbcontext var tran = db.connection.begintransaction();  db.executestorecommand("insert table1(col1,col2 ) select col1,col2 table1 id =   {0}", myoldrecordid);  command = @"select scope_identity ({0}) current_identity;"; mynewrecordid = (int)db.executestorequery<decimal>(command, "table1").first();   tran.commit(); db.connection.close(); 

see: reference

is best way new id, without corruption, other user record inserts?

actually having read more, old way, , "transactionscope" way go.

sorry writing in answer instead of comment: think you´re 99% right, far understand it, should use

scope_identity() 

instead of ident_current, come 100%