Why is dapper returning all zero's for Guid when doing a select but the guid value in table is set correctly?

Question

I'm using dapper to query data from table and then cast it to an object. When it is cast to the object the guid property is set to all zero's, but all the other props are set correct.

public class UserStuff
{
    public int Id { get; set; }
    public Guid UId { get; set; }
}


public async Task<UserStuff> GetUserStuff(Guid uId){
  using(IDbConnection conn = Connection){
    string sQuery = "SELECT TOP 100 id, u_id " +
                    "FROM TestTable WHERE u_id = @u_id ";
    conn.Open();
    var result = await conn.QueryAsync<UserStuff>(sQuery, new { u_id = uId });
    return result.FirstOrDefault();
  }
}

Example SQL data:

id | u_id

5 | C9DB345B-D460-4D71-87E0-D9A3B5CE1177

It's returning : 5 for the id, and all zero's for the guid

Answer 1

Core problem here is that your column name and property name are different. Hence, even though the value is returned by database as a result of SQL query, it is not mapped to your property. As the datatype of your property is GUID, it holds its default value - all zeros.

Dapper mapping works on conventions; bit broad topic to discuss. For your specific problem, your column name and property name should match. There are other ways as well to make the mapping happen correctly if those are different.

I will propose simple solutions here:

1. You can instruct Dapper to ignore underscores from column name while mapping.

   using Dapper;
   Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
   

   Set this property at the startup of the project somewhere.

2. Use column aliases in SQL query. Change your SQL query to something like below:

   SELECT TOP 100 id, u_id as UId....
   

   This way, without changing the column name and property name, you will be able to fill up the property correctly as the mapping is corrected now.

3. Either change the column name to match with property name or vice versa. I do not think this is practical.

Apart for above, there is also a custom mapping available with dapper.