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

[dumbledad]

My Id is defined in tSQL as

id NVARCHAR (128) DEFAULT (NEWID()) NOT NULL

and in C# as

public Guid Id { get; set; }

Dapper generates an error Invalid cast from 'System.String' to 'System.Guid'. One solution (from here) is to add a private IdString thus

private string IdString { get; set; }
public Guid Id
{
    get
    {
        return new Guid(IdString);
    }
    set
    {
        IdString = value.ToString();
    }
}

and changing the tSQL from id to Id AS IdString.

It's a bit painful not to be able to use SELECT * because of that alias.

Using Guid as an id column seems common practice.

Should there not be a built in mechanism for casting between Guids and strings?

[NickCraver]

First have to ask about the constraints here: Why aren't you using an actual UNIQUEIDENTIFIER (the GUID type) in SQL?

[mgravell]

Yes, I'd agree with you: we should make this work. Can I check - is this using just the core dapper package (Query<Foo>, etc)? Or is this using "Contrib", etc?

Out of mild curiosity, is there a reason you aren't using the "uniqueidentifier" type (or similar) in the database?

On 1 February 2016 at 13:29, Tim Regan [email protected] wrote:

My Id is defined in tSQL as

id NVARCHAR (128) DEFAULT (NEWID()) NOT NULL

and in C# as

public Guid Id { get; set; }

Dapper generates an error Invalid cast from 'System.String' to 'System.Guid'. One solution (from here http://stackoverflow.com/a/31607532/575530) is to add a private IdString thus

private string IdString { get; set; } public Guid Id { get { return new Guid(IdString); } set { IdString = value.ToString(); } }

and changing the tSQL from id to Id AS IdString.

It's a bit painful not to be able to use SELECT * because of that alias.

Using Guid as an id column seems common practice.

Should there not be a built in mechanism for casting between Guids and strings?

— Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/447.

Regards,

Marc

[NickCraver]

Also as a workaround for others if this is a real constraint, there's a simpler approach:

Convert(UniqueIdentifier, id) as id

[dumbledad]

Question: Why not use UNIQUEIDENTIFIER in SQL? Answer: Ignorance, I will now! I am converting an Azure Mobile Services API and Database. That uses Entity Frameworks. The SQL of the corresponding column there is NVARCHAR(128), and the (cut down) corresponding CREATE statement is

CREATE TABLE [vcollectapi].[Users](
    [Id] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_vcollectapi.Users] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [vcollectapi].[Users] ADD  DEFAULT (newid()) FOR [Id]

Question: Is this using just the core dapper package? Answer: Yes; though I do also load Rainbow (but the class that's used in is dormant).

I'll swap NVARCHAR(128) to UNIQUEIDENTIFIER and try Nick's workaround.

[NickCraver]

@dumbledad if you switch the type, no need for any workaround at all - types will match and it'll just work :)

[dumbledad]

Where's the 'like' button ;-)

[mgravell]

I will try and fix this for completionist reasons, but: changing to uniqueidentifier is the best approach; it avoids formatting concerns, malformed data, etc - and uses 16 bytes instead of 260 bytes (128 utf-16 plus length). And most systems should instantly recognize it and understand your intent. On 1 Feb 2016 1:52 pm, "Nick Craver" [email protected] wrote:

@dumbledad https://github.com/dumbledad if you switch the type, no need for any workaround at all - types will match and it'll just work :)

— Reply to this email directly or view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/447#issuecomment-177979919 .

[benpittoors]

+1 for this enhancement from me too.

[agsydney]

+1 for me too please (using mysql nvarchar(64) mapped to a Guid in .net)

[mtrtm]

Would be great when the poor souls using MySQL have a binary(16) representing a GUID and cannot map using Dapper.

[mgravell]

There is a beta on nuget that may fix this. Any volunteers?

On Tue, 8 Nov 2016 18:55 mtrtm, [email protected] wrote:

Would be great when the poor souls using MySQL have a binary(16) representing a GUID and cannot map using Dapper.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/447#issuecomment-259225725, or mute the thread https://github.com/notifications/unsubscribe-auth/AABDsDs3hQ1E6KUlviEiKkumgLB9wyV7ks5q8MWrgaJpZM4HQicA .

[mtrtm]

Does the beta only support char(32) in MySQL to .net Guid, or does it also support binary(16) to Guid?

[benpittoors]

It's not an issue for me right now (did some casting at the db side), but if you really want to know @mtrtm then I suggest you test it out and post your reproducible results right here. It will help the actual developers/contributors to fix it.

[mtrtm]

Hey guys,

It is unclear exactly what you would like tested, but I will probably have some time over the next day or two if you would like to provide what you want tested, what version you'd like me to test etc.

[mgravell]

Yeah, I think nuget dropped my upload. Will redo tomorrow.

On 8 Nov 2016 11:43 p.m., "mtrtm" [email protected] wrote:

Hey guys,

It is unclear exactly what you would like tested, but I will probably have some time over the next day or two if you would like to provide what you want tested, what version you'd like me to test etc.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/447#issuecomment-259295300, or mute the thread https://github.com/notifications/unsubscribe-auth/AABDsPyvt1PSYfZW3QyK94CXKbjJTProks5q8QkxgaJpZM4HQicA .

[normanthesquid]

@mgravell Did the beta upload ever make it to nuget? we are looking at switching over to binary(16), and dapper really doesn't like it.

Edit: nevermind, binary(16) seems to work fine in 1.50.3-beta1. Now i just need to know a release date. Is there one in mind?

[mgravell]

Yes, however based on the feedback in this thread, I suspect we should revert this feature - it is unreliable between vendors.

https://www.nuget.org/packages/Dapper/1.50.3-beta1

On 6 December 2016 at 21:45, normanthesquid [email protected] wrote:

@mgravell https://github.com/mgravell Did the beta upload ever make it to nuget? we are looking at switching over to binary(16), and dapper really doesn't like it.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/447#issuecomment-265282373, or mute the thread https://github.com/notifications/unsubscribe-auth/AABDsLytDC4cU51YqHb-mPSVFFaJ1LRaks5rFddfgaJpZM4HQicA .

-- Regards,

Marc

[NickCraver]

Update: this was reverted due to vendor issues underneath. We simply can't support this convenience mapping and instead encourage the correct type. I'm adding it to the overall issue like in V2 to consider for the [Column] mapping.

[supersonicclay]

It seems with this commit, we may be able to accomplish the MySql binary(16) to C# Guid. However, it isn't in 1.50.2... https://github.com/StackExchange/dapper-dot-net/commit/8aa10a0237f0b419fc063eff95f6f01b617cb4c3

Something like this:

Main()
{
  SqlMapper.AddTypeHandler(new MySqlGuidTypeHandler());
  SqlMapper.RemoveTypeMap(typeof(Guid));
  SqlMapper.RemoveTypeMap(typeof(Guid?));
}

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

    public override Guid Parse(object value)
    {
      return new Guid(FlipEndian((byte[]) value));
    }

    internal static byte[] FlipEndian(byte[] oldBytes)
    {
      var newBytes = new byte[16];
      for (var i = 8; i < 16; i++)
        newBytes[i] = oldBytes[i];

      newBytes[3] = oldBytes[0];
      newBytes[2] = oldBytes[1];
      newBytes[1] = oldBytes[2];
      newBytes[0] = oldBytes[3];
      newBytes[5] = oldBytes[4];
      newBytes[4] = oldBytes[5];
      newBytes[6] = oldBytes[7];
      newBytes[7] = oldBytes[6];

      return newBytes;
    }
  }

[mgravell]

The ultimate problem here is that there is no reliable way to automate when to do this and when not to, especially since various interception techniques can sit between dapper and the connection (so we can't just check for MySqlConnection). If it is manual: people will not do it consistently and will often not even realise they've been corrupting their data until they try accessing it a different way. It becomes actively harmful.

On 6 Feb 2017 5:20 a.m., "Clay Anderson" [email protected] wrote:

It seems with this commit, we may be able to accomplish the MySql binary(16) to C# Guid. 8aa10a0 https://github.com/StackExchange/dapper-dot-net/commit/8aa10a0237f0b419fc063eff95f6f01b617cb4c3

Something like this:

Main() { SqlMapper.AddTypeHandler(new MySqlGuidTypeHandler()); SqlMapper.RemoveTypeMap(typeof(Guid)); SqlMapper.RemoveTypeMap(typeof(Guid?)); }

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

public override Guid Parse(object value)
{
  return new Guid(FlipEndian((byte[]) value));
}

internal static byte[] FlipEndian(byte[] oldBytes)
{
  var newBytes = new byte[16];
  for (var i = 8; i < 16; i++)
    newBytes[i] = oldBytes[i];

  newBytes[3] = oldBytes[0];
  newBytes[2] = oldBytes[1];
  newBytes[1] = oldBytes[2];
  newBytes[0] = oldBytes[3];
  newBytes[5] = oldBytes[4];
  newBytes[4] = oldBytes[5];
  newBytes[6] = oldBytes[7];
  newBytes[7] = oldBytes[6];

  return newBytes;
}

}

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/StackExchange/dapper-dot-net/issues/447#issuecomment-277589028, or mute the thread https://github.com/notifications/unsubscribe-auth/AABDsPp9mR1HjWQw7wh6VVQ6z0dcKRdgks5rZq2TgaJpZM4HQicA .

[supersonicclay]

@mgravell I was indeed able to customize the way Dapper converts the Guid type with my code above!

I did this with 1.50.2 and cherry-picked commit 8aa10a (https://github.com/StackExchange/dapper-dot-net/commit/8aa10a0237f0b419fc063eff95f6f01b617cb4c3)

I'm not sure what else was in 1.50.3-beta1, but with 1.50.2 + https://github.com/StackExchange/dapper-dot-net/commit/8aa10a0237f0b419fc063eff95f6f01b617cb4c3, I am able to control the way Dapper converts the Guid type (both for selects and for parameters when inserting, where clauses, etc).

[programcsharp]

Looks like @claycephas's fix is (or will be) in 1.50.3. Interestingly enough, my guids sourced from iBatis don't need an endian flip.

[phillijw]

I think the only right answer is for DBs to implement a proper Guid type

[vpopescu]

I think this is also happening using MySQL when using a view that converts binary(16) to string using BIN_TO_UUID().. e.g. my view has a column defined like:

bin_to_uuid(UnderlyingBin16ID,1) as Id

[toddmeinershagen]

While I agree that every db vendor should support a unique identifier data type, Snowflake current doesn't support it. Is the registration of a custom mapper the only way to handle this?

[mgravell]

Certainly not the only way. Arguably a better way is to only use types that map to your RDBMS at the tier that interacts with it, and worry about any other mappings externally. But yes, it is one possible way.