How to fix DapperExtensions error after I Insert data and get the returned id

Question

I use Dapper-Extentions to pull and push data to database

I use unsigned int id as my Primary key in database and in the class as well. my class looks like this

public class Product {
    [Column("id")]
    public uint Id { get; set; }
}

My Mapper Class looks like this

public class ProductMap : ClassMapper<Product>
{
    public ProductMap()
    {
        Table("Product");                    
        this.Map(typeof(Product).GetProperty("Id")).Key(KeyType.Identity);
    }
}

I insert data like this

using DapperExtensions;

public virtual uint Add(T item)
{
    using (conn)
    {
        return Convert.ToUInt32(conn.Insert<T>(item)); // System.ArgumentException: 'Object of type 'System.Int32' cannot be converted to type 'System.UInt32'.'`
    }
}

When I insert data into database, the item get inserted into the database without issues, However the Insert function keeps returning the following error :

'Object of type 'System.Int32' cannot be converted to type 'System.UInt32'.'

How can I possibly fix that ?

Answer 1

The dynamic Insert<T> method of Dapper Extensions can return the newly generated ID of any type.

/// Executes an insert query for the specified entity, returning the primary key.
/// If the entity has a single key, just the value is returned.
/// If the entity has a composite key, an IDictionary<string, object> is returned with the key values.
/// The key value for the entity will also be updated if the KeyType is a Guid or Identity.

It does this with IdentitySql method in SqlGeneratorImpl class.
This can be confirmed with following code:

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public bool Active { get; set; }
}

public sealed class ProductMapper : ClassMapper<Product>
{
    public ProductMapper()
    {
        Schema("dbo");
        Table("Products");
        Map(x => x.Id).Key(KeyType.Guid);
        AutoMap();
    }
}

Product product = new Product();
product.Name = "Product 1";
product.Active = true;
using(SqlConnection conn = new SqlConnection(connString))
{
    DapperExtensions.DapperExtensions.Insert<Product>(conn, product, null, null);

    Guid customerID = product.Id;
    product = null;

    product = DapperExtensions.DapperExtensions.Get<Product>(conn, customerID, null, null);
}

As you said, INSERT operation is happening fine. Then Dapper Extensions extract the newly generated identity value and tries to assign it to your Product.Id property.

Now, datatype of what is returned (column value) is int which is signed. Datatype of Id property is uint which is unsigned. Though the length of both the data types is same, type of the data (signed and unsigned) they can hold is different and hence the error.

You should change data type of your Id property to int like below:

public int Id { get; set; }

---

As you said in your answer, you have to keep the property uint, following is what I propose:

Add an extra property to your class just as a holder/copy like below:

public class Product {
    [Column("id")]
    public int Id { get; set; }//Map this as Identity.

    public uint IdCopy { get { return Convert.ToUInt32(Id); } }//Readonly; Exclude this from mapping
}

Answer 2

I really needed to store my data IDs as uint and found a solution for that int32 to uint conversion error.

In the DapperImplementor.cs class, inside the function public dynamic Insert<T> {...}

There is a variable int identityInt = 0; used to hold the id before assigning it to the corresponding object id

The issue was resolved after I changed it's type to uint identityInt = 0;