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, 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 notifications@github.com 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
#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" notifications@github.com 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
#447 (comment)
.

[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, notifications@github.com 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
#447 (comment),
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.