Invalid cast when returning mysql LAST_INSERT_ID() using dapper.net

Question

This question has been covered for MSSQL here:

How do I perform an insert and return inserted identity with Dapper?

but this solution does not work with mysql.

To cast the LAST_INSERT_ID() to integer with mysql you have to do this:

SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);

The stack trace is:

Dapper.<QueryInternal>d__13`1.MoveNext() in sqlMapper.cs:607
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +159
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +36
   Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in sqlMapper.cs:535

Has anyone resolved this issue in MySQL?

EDIT:

I've managed to make this work with the following:

var id = connection.Query<ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").Single();

Perhaps not ideal, but it works.

Answer 1

I'll leave this here as an answer for anyone else who might search on this problem.

I've managed to make this work with the following:

var id = connection.Query<ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").Single();

Perhaps not ideal, but it works.