Map string to guid with Dapper

Question

I'm using Dapper to hammer out some load testing tools that need to access a PostgreSQL database. This particular version of PostgreSQL does not support GUIDs natively, so GUID values are stored as 32 character strings. The values are converted to strings using someGuid.ToString("N"), conversion back to Guid can be done using new Guid(stringValueFromColumn).

My question is how do I get Dapper to read the strings and convert them back to Guids?

I tried modifying the DbType mapping but that doesn't work.

Though you mention this prominently in your question, I missed your mention that you are using a database that "does not support GUIDs natively". It is worth note that in databases that do, one can map UNIQUEIDENTIFIER in tSQL (or similar) to Guid in C# ([e.g.](https://github.com/StackExchange/dapper-dot-net/issues/447)) — dumbledad, Feb 01 '16 at 14:17

score 74 · Answer 1 · edited Dec 01 '20 at 13:05

74

I'm using MySql but it has the same problem since I store the Guid as a string. To fix the mapping without having to alias the column i used the following:

public class MySqlGuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
    public override void SetValue(IDbDataParameter parameter, Guid guid)
    {
        parameter.Value = guid.ToString();
    }

    public override Guid Parse(object value)
    {
        return new Guid((string)value);
    }
}

And in my Startup.cs:

public void ConfigureServices(IServiceCollection services)
    {
        SqlMapper.AddTypeHandler(new MySqlGuidTypeHandler());
        SqlMapper.RemoveTypeMap(typeof(Guid));
        SqlMapper.RemoveTypeMap(typeof(Guid?));
    }

edited Dec 01 '20 at 13:05

StayOnTarget

11,743
10
52
81

answered Sep 13 '18 at 18:40

Cpt.Ohlund

2,589
1
20
15

1

Not sure why this isn't the most upvoted answer it works perfectly and is way more official than these other methods – drowhunter Sep 25 '19 at 21:48
1

Fantastic, better than my solution. – Jack Pettinger Nov 11 '19 at 10:52
Really nice, I just used this on Sqlite – TlonXP Feb 15 '20 at 02:49
Work as charm, thank you! Totaly most simple solution! – XLR8 Apr 08 '20 at 09:48
Thanks, I followed the first answer but I think this is most suitable – Leonardo Lima May 13 '20 at 00:21
2

Someone **PLEASE** mark this as the correct answer. This method correctly handles mapping GUID fields in anonymous query parameters as well. The `RemoveTypeMap` calls are important as well. – Arshia001 Nov 05 '20 at 12:33
2

In MySql it is also possible, to create the column as a binary(16) and use `guid.ToByteArray()` and `new Guid((byte[])value)` in this TypeHandler. – Hinek Apr 11 '21 at 18:55
Wouldn't it need a class to nullable guid? Per example: MySqlNullableGuidTypeHandler – Guilherme Bley Mar 22 '23 at 17:14

score 28 · Accepted Answer · answered May 07 '11 at 11:47

28

Perhaps the simplest way to do this (without waiting on dapper) is to have a second property:

public Guid Foo {get;set;}

public string FooString {
    get { return Foo.ToString("N"); }
    set { Foo = new Guid(value); }
}

And in your query, alias the column as FooString.

Of course, then this prompts the question: should dapper support private properties for this type of thing? To which I say: probably.

answered May 07 '11 at 11:47

Marc Gravell

1,026,079
266
2,566
2,900

Well, that is a bit cleaner than hacking away at Dapper. Allowing private properties (or fields) to be set would be great. – Marnix van Valen May 09 '11 at 10:19
@Marnix - yes, I intend adding those – Marc Gravell May 09 '11 at 10:26
@Marnix - now all exists, btw (in source - I haven't de-deployed the nuget package) – Marc Gravell May 09 '11 at 12:56
1

@MarcGravell Could you update your answer with an example of a private property? – Jack Pettinger Jul 24 '15 at 09:30
@MarcGravell thanks. I'm hitting this problem with SQLite but am trying to use Get in the Contrib library. This means that I'm going to have to use Query instead. – Christian Findlay Mar 06 '20 at 03:25

score 8 · Answer 3 · edited Dec 01 '20 at 13:07

It looks like there is UUID type in PostgreSQL already. But @Cpt.Ohlund's solution is still great for MySQL/MariaDB in 2020.

But the solution might cause problems itself.

When VARCHAR(36) is used for System.Guid the following exception is thrown:

System.InvalidCastException: Invalid cast from 'System.String' to 'System.Guid'.

@Cpt.Ohlund's solution makes it work.

But if the column is CHAR(36) then it is mapped to System.Guid automatically! And if @Cpt.Ohlund's solution is applied there will be another exception:

System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'.

The exception is caused by an instance System.Guid passed to Parse(object value) instead of a string.

So the simple answer is to use CHAR(36) in MySQL and MariaDB and it will just work.

But if you need to handle any string type of the column you have to use an improved @Cpt.Ohlund's solution:

public class MySqlGuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
    public override void SetValue(IDbDataParameter parameter, Guid guid)
    {
        parameter.Value = guid.ToString();
    }

    public override Guid Parse(object value)
    {
        // Dapper may pass a Guid instead of a string
        if (value is Guid)
            return (Guid)value;

        return new Guid((string)value);
    }
}

And register it using SqlMapper.AddTypeHandler().

score 5 · Answer 4 · edited Dec 01 '20 at 13:08

5

This is an old question but I feel it needs updating as Dapper now supports private properties, which Marc referenced to in his answer.

private String UserIDString { get; set; }
public Guid UserID
{
    get
    {
        return new Guid(UserIDString);
    }
    private set
    {
        UserID = value;
    }
}

Then in SQL give your ID column an alias to map it to the private property and not the actual property:

SELECT UserID AS UserIDString FROM....

edited Dec 01 '20 at 13:08

StayOnTarget

11,743
10
52
81

answered Jul 24 '15 at 09:56

Jack Pettinger

2,715
1
23
37

Did you mean `UserIDString = value;` instead of `UserID = value;` ? Otherwise I think you've got an ∞ loop. – StayOnTarget Dec 01 '20 at 13:06

score 2 · Answer 5 · answered May 06 '11 at 10:07

I hacked a solution together. As far as I can tell, there is no way to instruct Dapper to generate alternate binding code for a particular type so I modified the GetClassDeserializer method to force the unbox type to string if the property is a guid. Next I re-used the code that generates a constructor call for enums.

Here's the modified code snippet (starting at line 761 of rev. rf6d62f91f31a) :

// unbox nullable enums as the primitive, i.e. byte etc
  var nullUnderlyingType = Nullable.GetUnderlyingType( item.Info.Type );
  var unboxType = nullUnderlyingType != null && nullUnderlyingType.IsEnum ? nullUnderlyingType : item.Info.Type;
  if( unboxType == typeof(Guid))
  {
    unboxType = typeof (string);
  }
  il.Emit( OpCodes.Unbox_Any, unboxType ); // stack is now [target][target][typed-value]
  if (  ( item.Info.Type == typeof( Guid ) && unboxType == typeof( string ) ) 
        || ( nullUnderlyingType != null && nullUnderlyingType.IsEnum ) )
  {
    il.Emit( OpCodes.Newobj, item.Info.Type.GetConstructor( new[] { nullUnderlyingType ?? unboxType} ) );
  }

  il.Emit( OpCodes.Callvirt, item.Info.Setter ); // stack is now [target]

type coercion is something I deliberately avoided, would you mind posting a feature request on our bug tracker to see if there it makes sense to accommodate, keep in mind there will be spectacular explosion if the guid in postgres happens to be 31 chars long ... — Sam Saffron, May 07 '11 at 11:14
@Sam - meh, GIGO; personally this wouldn't bother me hugely. And there is prior in that L2S does *some* conversions here (including enums via name as string). — Marc Gravell, May 07 '11 at 11:43

score 0 · Answer 6 · answered May 13 '20 at 00:19

I hope that can help.

I didn't have to use alias in my query. What I did:

public abstract class Entity
{
    protected Entity()
    {
        Id = Guid.NewGuid().ToString();
    }

    public string Id
    {
        get; set; 
    }
}

And in your table the type as varchar

Map string to guid with Dapper

6 Answers6

Linked