How to compare nullable guid value with guid in linq query?

Question

I get an error when trying to execute:

Nullable<Guid> ng = corpid; var qry1 = from c in entities.Transactions join p in entities.Products on c.CorporationId equals (Nullable<Guid>) p.CorporationId where c.Branch == br && c.AccountNumber == accountnumber && c.CorporationId == ng orderby c.TransactionDate descending select new { Date = c.TransactionDate, RefNo = c.ReferenceNumber, DlvryAcct = c.DeliveryAccount, Desc = p.Description, GasQty = c.GasQuantity, Total = c.Amount, Balance = c.Balance }; 

This post is:

  LINQ to Entities does not recognize the method
 'System.Linq.IQueryable`1 [f__AnonymousType1`7 [System.Nullable`1 [System.DateTime],
   System.String, System.String, System.String, System.Nullable`1 [System.Decimal],
   System.Nullable`1 [System.Decimal], System.Nullable`1 [System.Decimal]]]
 Reverse [f__AnonymousType1`7] (System.Linq.IQueryable`1 [f__AnonymousType1`7 [System.Nullable`1 [System.DateTime],
   System.String, System.String, System.String, System.Nullable`1 [System.Decimal],
   System.Nullable`1 [System.Decimal], System.Nullable`1 [System.Decimal]]]) '
 method, and this method cannot be translated into a store expression.

I do not think the cast to nullable guid command works here. c.CorporationId is the null value of guid, but p.corporationid is just a guide.

Any suggestions?

Answer 1

Have you tried to abandon casting and equate c.CorporationId with ng.Value :?:

  Nullable<Guid> ng = corpid; var qry1 = from c in entities.Transactions join p in entities.Products on c.CorporationId equals p.CorporationId where c.Branch == br && c.AccountNumber == accountnumber && c.CorporationId == ng.Value orderby c.TransactionDate descending select new { Date = c.TransactionDate, RefNo = c.ReferenceNumber, DlvryAcct = c.DeliveryAccount, Desc = p.Description, GasQty = c.GasQuantity, Total = c.Amount, Balance = c.Balance }; 

Answer 2

It seems to be complaining about an anonymous constructor in the select clause. c.TransactioDate , c.GasQuantity , c.Amount and c.Balance all look like Nullable. Try something like this to see if these fields are a problem.

 Nullable<Guid> ng = corpid; var qry1 = from c in entities.Transactions join p in entities.Products on c.CorporationId equals (Nullable<Guid>) p.CorporationId where c.Branch == br && c.AccountNumber == accountnumber && c.CorporationId == ng.Value orderby c.TransactionDate descending select new { Date = c.TransactionDate.Value, RefNo = c.ReferenceNumber, DlvryAcct = c.DeliveryAccount, Desc = p.Description, GasQty = c.GasQuantity.Value, Total = c.Amount.Value, Balance = c.Balance.Value }; 

Answer 3

This is an old question, but since there is no answer, here is skinny. In C #, Guid is an object that cannot be nullified, so you cannot bind null to Guid, but you can display Null to Guid ?, so here is the solution:

 var qry1 = from c in entities.Transactions join p in entities.Products on c.CorporationId equals p.CorporationId where c.Branch == branch && c.AccountNumber == accountNumber && ((Guid?)c.CorporationId).Value == null // This is the secret sauce orderby c.TransactionDate descending select new { Date = c.TransactionDate, RefNo = c.ReferenceNumber, DlvryAcct = c.DeliveryAccount, Desc = p.Description, GasQty = c.GasQuantity, Total = c.Amount, Balance = c.Balance }; 

But probably I would do it:

 var qry1 = from c in entities.Transactions.Where(t => ((Guid?)t.CorporationId).Value == null) join p in entities.Products on c.CorporationId equals p.CorporationId where c.Branch == branch && c.AccountNumber == accountNumber orderby c.TransactionDate descending select new { Date = c.TransactionDate, RefNo = c.ReferenceNumber, DlvryAcct = c.DeliveryAccount, Desc = p.Description, GasQty = c.GasQuantity, Total = c.Amount, Balance = c.Balance }; 

But you have to ask a question if you need to indicate why the model has this column, which is considered invalid (if it was configured correctly, you probably would not have encountered the need to quit at this stage).