Get the last automatically inserted identifier in the identity column from the database using Entity Framework

Question

I use the code below to get the new inserted identifier in the auto-identification column from the database.

using (TestEntities entities = new TestEntities()) { T2 t2 = new T2(); t2.Value = "some value"; entities.AddToT2(t2); entities.SaveChanges(); Console.WriteLine(t2.ID); } 

It works fine when I connect my application to MS Sql, but if I connect to Mysql, it always returns 0 (zero).

Is there any other way to get the last inserted id using Entity Framework?

Thanks...

Answer 1

Finally, I got soln. There was only one column in the table into which I inserted the row - "ID" with auto identity = true. When I add another column with a null value with any name in the table, it starts working with my sql too !!! I think the problem is in my sql connector, not the entity.

Thanks:)

Answer 2

I had to change the authentication mechanism to get the ID of the inserted record after SaveChanges (). I am using Visual Studio 2010 and ORM Telerik OpenAccess.

Open the Entity model diagram in Visual Studio. Right-click the table of interest and select "Properties." Verify that the Identity Mechanism property is set to DatabaseServerCalculated.

Answer 3

In MySQL, you can get the last inserted field identifier with auto increment using

MySQL only

 SELECT last_insert_id() as Last_ID 

This will work provided . Entity Framework uses SQL to manage a MySQL database.
A simple test will answer what I think.

Note that you will only get the last inserted identifier from the current connection (usually this is what you want).
There is no way (I know) to get the last inserted id of other connections except by doing

 SELECT MAX(id) FROM sometable as Last_ID 

What will work if the insert from another connection has been completed.

Make it portable to other DBs
If you want to make this solution portable to another database, you can make the saved function as follows:

 CREATE FUNCTION GetLastID: integer; BEGIN DECLARE lastid: integer; SELECT last_insert_id() INTO lastid; RETURN lastid; END; 

You can then modify this stored procedure if you modify the DB-backend.
No need to rewrite any other code.

SELECT GetLastID () as Last_ID