C# Dapper get last insert id returns null MySQL

Question

I have two tables Partners and Partner_Address, I want to store partner addresses in another database. For that I use Transaction in Dapper.

After executing the query to insert new partner I try to get last insert id and then to pass that ID to address object, but all time I get 0. Partner Record was successfully inserted but last insert id is 0.

Tables:

// Partner Address entity

public class AdreseKorisnika   
{
    public int id { get; set; }
    public int partnerId { get; set; }  // partnerID
    public int adresaId { get; set; }  // addressID
    public string broj { get; set; }  // number
    public int status { get; set; }
    public int primarna { get; set; } // primary
}


public class Partner
{
    public int id { get; set; }
    public string naziv { get; set; }   // name        
    public string telefon { get; set; }  // phone
    public string email { get; set; }
    /// etc...

What I try:

public int InsertWithAdresses(Partner partner, AdreseKorisnika adreseKorisnika)
{                
    using (Conn)
    {
        string partnerQuery = @"INSERT INTO Partner(naziv, pib, maticni_br, telefon, email, web_sajt, status, created) 
                                VALUES(@naziv, @pib, @maticni_br, @telefon, @email,@web_sajt, @status, @created);";

        string addressQuery = @"INSERT INTO Adrese_Korisnika(partnerId, adresaId, broj, status, primarna) 
                                VALUES(@partnerId, @adresaId, @broj, @status, @primarna);";
        int affectedRows = 0;

        using (var transaction = Conn.BeginTransaction())
        {
            affectedRows = Conn.Execute(partnerQuery, partner, transaction: transaction);

            int id = Conn.Query<int>("SELECT LAST_INSERT_ID();").First();

            var addrs = Conn.ExecuteScalar<int>(addressQuery, 
                new
                {
                    partnerId = id,  // <-- here I try to pass last insert id from partnerQuery but it is always 0
                    adresaId = adreseKorisnika.adresaId,
                    broj = adreseKorisnika.broj,
                    status = adreseKorisnika.status,
                    primarna = adreseKorisnika.primarna
                }, 
                transaction: transaction);

            transaction.Commit();
        }

        return affectedRows;
    }
}

I want my Partners to have one ore more addresses in another table. I also tried this: Dapper MySQL return value

UPDATE

DEBUG

AFAIK that code should work if the Partner table has ID set as AUTOINCREMENT. The MySqlConnection has also a non standard property called LastInsertedId. Did you check if this property is also zero? — Steve, Mar 22 '20 at 14:06
that is just entity represetnatnion. Ofcourse my table have `autoincrement` — Ivan, Mar 22 '20 at 14:25
I tried to reproduce your problem but I get the correct value at every insert. I would try (but just out of better ideas and only to exclude some paths) to remove the transaction and check if somethings changes and, as second attempt, I would change the storage type from MyIsam to InnoDB. As I have said, these are just like desperation moves because I have no more idea to try. — Steve, Mar 22 '20 at 15:03
Why is your query not in the same transaction as your insert? That is likely the problem. — Robert McKee, Mar 23 '20 at 06:20

score 0 · Answer 1 · edited Mar 23 '20 at 10:22

I solved the problem like this:

public int InsertWithAdresses(Partner partner, AdreseKorisnika adreseKorisnika)
{                
    using (Conn)
    {
        string lastInsertID = @"
                            INSERT INTO Partner(naziv, pib, maticni_br, telefon, email, web_sajt, status, created) 
                            VALUES(@naziv, @pib, @maticni_br, @telefon, @email,@web_sajt, @status, @created);

                            INSERT INTO Adrese_Korisnika(partnerId, adresaId, broj) 
                            VALUES(LAST_INSERT_ID(), @adresaId, @broj);

                            SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);";


         using (var transaction = Conn.BeginTransaction())
         {
             var affectedRows = Conn.Query<int>(lastInsertID,
                 new
                 {
                     naziv = partner.naziv,
                     pib = partner.pib,
                     maticni_br = partner.maticni_br,
                     telefon = partner.telefon,
                     email = partner.email,
                     web_sajt = partner.web_sajt,
                     status = partner.status,
                     created = partner.created,
                     adresaId = adreseKorisnika.adresaId,
                     broj = adreseKorisnika.broj,

                },
                transaction: transaction);

            return 1;
        };
    }

}

C# Dapper get last insert id returns null MySQL

1 Answers1