NH-3426 - Wrong result when converting Guid to string

[nhibernate-bot]

Oskar Berggren created an issue — 18th March 2013, 9:06:30:

Having a sqlite database where a column contains guids stored as blobs.

In the select clause of a linq query, ToString() is applied on the Guid property of the model object. NHibernate will forward this to the SQL as

     select
        cast(Id as char) as col_2_0_ 

Unfortunately, the resulting strings won't contain the intended standard string representations of GUID, but instead the raw bytes directly interpreted as characters, which is something completely different.

[lillo42]

Hi,

I found how to fix this bug, We need change query to:

select entity0_.Id as col_0_0_ from Entity entity0_

I'm thinking to create a new function "strguid":

RegisterFunction("strguid", new SQLFunctionTemplate(NHibernateUtil.String, "?1"));

What do you think? Is it a good solution?

[fredericDelaporte]

I do not currently understand what is exactly the trouble, so even less what could be a good solution.
I think this issue first need a test case demonstrating the trouble.

[oskarb]

Actually, this SQL:

select
        cast(Id as char) as col_2_0_ 

worked fine assuming that BinaryGuid=False was given in the connection string.

The new SQL from NH 5.2:

    select
        substr(hex(entity0_.Id),
        7,
        2) || substr(hex(entity0_.Id),
        5,
        2) || substr(hex(entity0_.Id),
        3,
        2) || substr(hex(entity0_.Id),
        1,
        2) || '-' || substr(hex(entity0_.Id),
        11,
        2) || substr(hex(entity0_.Id),
        9,
        2) || '-' || substr(hex(entity0_.Id),
        15,
        2) || substr(hex(entity0_.Id),
        13,
        2) || '-' || substr(hex(entity0_.Id),
        17,
        4) || '-' || substr(hex(entity0_.Id),
        21)  as col_0_0_ 
    from
        Entity entity0_

while keeping BinaryGuid=False in the connection string, the new SQL generates something miserable:
Expected: equivalent to < "2a3208ff-4587-4582-91f6-aa2a00b67c3e", "f1c32b3f-1de1-4620-8055-aa2a00b67c38" >
But was: < "32336132-3830-6666-2D34-3538372D343538322D393166362D616132613030623637633365", "33633166-6232-6633-2D31-6465312D343632302D383035352D616132613030623637633338" >