In transaction, @@IDENTITY return other table id, is it a bug?

[youbl]

My project use: Dapper 1.50.2 Dapper.Contrib 2.0.0 DapperExtensions 1.6.3

and my code like this:

var sql = "insert into tb(name) select 'aaa' from dual where not exists(select 1 from tb where name='aaa')";
using(var connection = factory.CreateConnection())
using(var trans = connection.BeginTransaction())
{
    if(connection.Execute(sql, null, trans) > 0) 
    {
        return Convert.ToInt64(connection.ExecuteScalar("SELECT @@IDENTITY", null, trans));
    }
}
return 0;

while I published the code, I found "SELECT @@IDENTITY" returns other table's autoId, not tb's autoId? Is my code wrong? how to fix it?

[youbl]

I tried use single sql like : insert into tb(name) select 'aaa' from dual where not exists(select 1 from tb where name='aaa'; select @@IDENTITY

but it always return id, even if insert SQL failed.

[AdaskoTheBeAsT]

Maybe you have scope_identity in mind... https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15

[youbl]

Maybe you have scope_identity in mind... https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15

thanks, but my db is MySQL5.7.25-log, not SQL Server.

[AdaskoTheBeAsT]

My bad I see - what about last_insert_id? https://dev.mysql.com/doc/c-api/8.0/en/getting-unique-id.html

[youbl]

@AdaskoTheBeAsT @@IDENTITY This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. See it here: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

[AdaskoTheBeAsT]

Indeed you're right! Thanks :)

[fretje]

Might be the missing closing parenthesis in your "not exists" sql clause?

[youbl]

Might be the missing closing parenthesis in your "not exists" sql clause?

Thanks，this code is demo, my real code is right.

[davidein]

Anyone know if this is still an issue?

[mgravell]

This is simply a limitation of the underlying database engine. Dapper issues what it is told, and processes what it gets back; what happens at the server: not up to us. However! If you're using @@identity: stop doing that - use scope_identity() or something else more appropriate. The optional Dapper.Advisor analyzer package has rules that will detect you using @@identity and issue appropriate guidance (specifically: rule DAP203). The notes here are based on SQL Server, so if you have a different database engine: use the most appropriate alternative for that engine, but once again: what happens here isn't up to Dapper.