Using Dapper-dot-net, how do I map SQL uniqueidentifier column to a .Net type?

Question

I have an existing database that uses the SQL "uniqueidentifier" type, which looks like a different GUID format than a .Net GUID.

Question: Using Dapper, how do I map SQL uniqueidentifier column to .Net?

Issue: Using Dapper-dot-net, when I map the column using the .Net GUID type, it compiles and runs, but all of the .ToString() output displays the same incorrect GUID. (well it doesn't match what I see in SSMS)

When I try to map the column using the string type, I get a compiler error.

Ideas? Is this possible?

Answer 1

Aha! It appears that you must convert the SQL uniqueidentifier to a string in T-SQL and then map to to a string type in .Net.

string sql = @"SELECT TOP 100
               CONVERT(varchar(36), MyIdColumn) AS IdColumnString
               FROM MyTable";

conn.Query<string>(sql).ToList();

I assume special care would need to be taken converting back from .Net string to T-SQL uniqueidentifier on updates, but I have not yet tested that concern.

Answer 2

I also found that I could cast it to a varchar in T-SQL, but I wanted the actual GUID type in .NET.

With Dapper 1.38 (also using SQL Server as the underlying RDBMS), I had no trouble with mapping if I made the POCO property a Nullable<Guid>. A SqlGuid or a String did not work.

public class MapMe
{
    public Guid? GuidByDapper { get; set; }
}

class Program
{
    static void Main(string[] args)
    {
        using (var connection = Utility.GetConnection())
        {
            var mapped = connection.Query<MapMe>("SELECT NEWID() AS GuidByDapper").Single();
            Console.WriteLine("Guid: {0}", mapped.GuidByDapper);
        }
    }
}

As a Console app it spits out the following:

Guid: 85d17a50-4122-44ee-a118-2a6da2a45461