'System.Guid' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type

Question

EDIT: Calling

  var _data = context.Database.SqlQuery<InfoViewModel>(sql).FirstOrDefault();

if I ran the below sql in the sql management studio and its works without throwing any error but if I have the same query called using EF its throwing the below error message:

here is my SQL:

SELECT 
 FROM dbo.Clients LEFT OUTER JOIN  
 dbo.aspnet_Users AS c ON dbo.Clients.CollectionsAssignedTo = c.UserId 
 LEFT OUTER JOIN dbo.aspnet_Users AS b ON dbo.Clients.Auditors_Key = b.UserId 
 LEFT OUTER JOIN dbo.aspnet_Users AS a ON dbo.Clients.AcctRep = a.UserId 
 WHERE dbo.Clients.ClientPKID = 'ce723426-dert-4c36-980a-f5181870d0d4'

The cast to value type 'System.Guid' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type

that is not a valid GUID -- you can't have letters after 'f' in a GUID it is hex — Hogan, Feb 04 '15 at 19:30
http://guid.us/Test/GUID... This relevant? http://stackoverflow.com/questions/7187576/validation-of-guid — xQbert, Feb 04 '15 at 19:31
I'm still getting the same error even with this guid `ce723426-bfbe-4c48-980a-f5181870d0b4` — Nick Kahn, Feb 04 '15 at 19:32
@StuartLC: please reply as answer and I will accept as answer. — Nick Kahn, Feb 05 '15 at 02:44

score 6 · Accepted Answer · answered Feb 05 '15 at 05:54

The error is caused because one of Guid columns returned from your Sql query is NULL, and cannot be bound to your InfoViewModel entity because the matching property is defined as Guid, not Nullable<Guid> or Guid?.

A likely culprit here is that if a Left Outer Join fails to match, all columns in the right hand side table will be returned NULL. You haven't provided the SELECT columns, so we can only speculate, but for instance:

SELECT c.UserId, ... SomeOther columns
 FROM dbo.Clients 
 LEFT OUTER JOIN dbo.aspnet_Users AS c ON dbo.Clients.CollectionsAssignedTo = c.UserId
 ...

Here, if there is a Row in dbo.Clients with a value for CollectionsAssignedTo which does not have a corresponding dbo.aspnet_Users row with the same c.UserId, a row will be returned, however the c.UserId column will be NULL, and you will receive the error mentioned in your question. (The mere presence of LOJ over INNER JOIN could imply that failed joins are expected).

its just work me, i added .DefaultIfEmpty().FirstOrDefault(); end of linq query. because linq query can not return null if no record match in joining, thanks. — adnan, Jan 23 '17 at 03:19

score 1 · Answer 2 · answered Feb 04 '15 at 21:32

1

Your query returns a null value in one of the Guid columns. That null value cannot be put into a Guid property in your DTO class InfoViewModel. How would you convert null to Guid? Impossible.

Make the property accept null values.

answered Feb 04 '15 at 21:32

usr

168,620
35
240
369

'System.Guid' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type

2 Answers2