Error using Join on uniqueidentifier - convert uniqueidentifier to numeric

Question

I am using Fluent NHibernate on sqlserverce. Using NHibernate QueryOver I try to retrieve a row - NHibernate generate automatically a a join query and I get the following Exception:

[SQL: SELECT tag FROM CheckpointToProtectionGroup cp2pg 
      JOIN CheckpointStorageObject cp ON cp.id = cp2pg.checkpoint_id 
      JOIN ProtectionGroupCheckpointStorageObject pg ON pg.id = cp2pg.vpg_id 
      WHERE cp.CheckpointIdentifierIdentifier = 1111  AND 
            pg.ProtectionGroupIdentifierGroupGuid = 
               11111111-1111-1111-1111-111111111111]
---> System.Data.SqlServerCe.SqlCeException: 
     The conversion is not supported. 
     [ Type to convert from (if known) = uniqueidentifier, 
       Type to convert to (if known) = numeric ]

From what I see, it seems that it tries to convert the value - 11111111-1111-1111-1111-111111111111 to numeric but this value is a Guid field:

CheckpointToProtectionGroup checkpointToProtectionGroup = Session
            .QueryOver<CheckpointToProtectionGroup>()
            .JoinQueryOver( row => row.ProtectionGroup)
            .Where(row => row.ProtectionGroupIdentifier.GroupGuid == 
                   protectionGroupIdentifier.GroupGuid)
            .SingleOrDefault();

ProtectionGroupIdentifier.GroupGuid is of Guid type

score 1 · Accepted Answer · answered Jul 08 '12 at 17:22

1

Looks like your GroupGuid value is not correctly converted to SQL. It should have single quotes around the value.

pg.ProtectionGroupIndentifierGroupGuidId = '11111111-1111-1111-1111-111111111111'

SQL Server tried to convert left hand value from uniqueidentifier (Guid) to numeric, since the right hand value is numeric value - numeric subtract operation with few operands.

You have protectionGroupIdentifier.GroupGuid value in Where part of your QueryOver expression. Check if GroupGuid is indeed Guid property. If it's an object property, cast it to Guid.

answered Jul 08 '12 at 17:22

Miroslav Popovic

12,100
2
35
47

GroupGuid is Guid Property. but NHibernate don't figure it when I use JoinQueryOver. When QueryOver is activated on GroupGuid entity (ProtectionGroupCheckpointStorageObject) Nhibernate provides correct SQL with GroupGuid of type Guid - [Type: Guid (0)] – Haimon Jul 08 '12 at 19:16
It looks like a bug in NHibernate: When using a JoinQueryOver between 2 tables, NHibernate fail to convert simple Guid value to uniqueidentifier. Need your advice. Thanks – Haimon Jul 09 '12 at 09:08
1

Is ProtectionGroupIdentifier another entity, or a custom type, or component? Maybe the problem is with that. – Miroslav Popovic Jul 09 '12 at 13:32
ProtectionGroupIdentifier is a component. – Haimon Jul 09 '12 at 13:41
1

Hm, how about converting both sides to string in Where? Or just one and using Equals instead of ==. Not really sure if it will work. Maybe this wouldn't be a problem if you had a regular Guid property instead of component... I'm kind of out of ideas, so just trowing anything in. – Miroslav Popovic Jul 09 '12 at 13:58

Error using Join on uniqueidentifier - convert uniqueidentifier to numeric

1 Answers1