Getting Identity Columns after insert, SQL Server and MySQL

When inserting into a relational database (db), you can have the database automatically assign a value to the primary key of the row you are inserting. This is generally called the Identity Column.

Using Entity Framework (ef) or Entity Framework Core (ef core) (via an appropiate db provider such as Pomelo MySQL driver), an insert of a new entity into the db will generally retrieve for you the identity column value by adding a select query to the end of the insert query, unbeknownst to the developer.

However, when using raw sql or micro-ORMs like Dapper, you'll need to add this query yourself and depending on the db in question, you need to add specific sql to retrive this value for reference to other entities.

So here is the syntax for both SQL Server and MySQL:

SQL Server Syntax

insert into Persons(firstname,lastname) values(bob,smith);
select cast(scope_identity() as int);

MySQL Syntax

insert into Persons(firstname,lastname) values(bob,smith);
SELECT LAST_INSERT_ID();