Map string to guid with Dapper

问题:

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.

回答1:

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.

回答2:

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....

回答3:

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]

回答4:

I know this is an old question but for any one stumbling upon this thread like I did today I'll post my solution.

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?));
    }