5

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

haydnD
  • 2,225
  • 5
  • 27
  • 61
  • This seems to be a known issue with Dapper, see https://github.com/StackExchange/Dapper/issues/447 and https://stackoverflow.com/questions/5898988/map-string-to-guid-with-dapper). – Mitch Stewart May 08 '19 at 20:26
  • I'm not sure that the link you pasted is concerning querying existing guid from db. It looks like it's for creating guids. I'm trying to query an guid from sql. – haydnD May 08 '19 at 20:32
  • 1
    @ironman The property in your `UserStuff` class is named `UId`, but the column in your SQL table is called `u_id`. Is Dapper doing some name matching automatically to convert `u_id` to `UId`? If not, can you use an alias to return the value with the name `UId`? – Progman May 08 '19 at 20:51
  • @Progman so, are you saying that the naming convention in my entity has to be exact with the table column names? – haydnD May 08 '19 at 21:08
  • @ironman Looks like there is something called `DefaultTypeMap.MatchNamesWithUnderscores`. Maybe that can help. – Progman May 08 '19 at 21:18
  • @Progman your post is the answer. – haydnD May 08 '19 at 21:27

2 Answers2

7

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.

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
0

Dapper matches columns to properties if the types and properties are equal

Simplest way would be to adjust the query

    string sQuery = "SELECT TOP 100 id, u_id as uid " +
                    "FROM TestTable WHERE u_id = @u_id ";

Just a hint: You don't need to open the connection manually. Dapper does that for you and closes it, after the query finished or fails, if the connection-state is closed. In more complex scenarios this might be necessary but in your case it is not.

Another hint: Don't use underscores for column names in SqlServer. By convention in SqlServer we are supposed to use upper-camel-case column names.

alsami
  • 8,996
  • 3
  • 25
  • 36