'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

Answer 1

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).

Answer 2

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.