Unable to create a constant value - only primitive

Question

I have seen some questions related to this Exception here but none made me understand the root cause of the problem. So here we have one more...

var testquery = 
((from le in context.LoanEMIs.Include("LoanPmnt")
  join lp in context.LoanPmnts on le.Id equals lp.LoanEMIId
  where lp.PmntDtTm < date && lp.IsPaid == false
  && le.IsActive == true && lp.Amount > 0
  select new ObjGetAllPendingPmntDetails
  {
      Id = lp.Id,
      Table = "LoanEMI",
      loanEMIId = lp.LoanEMIId,
      Name = le.AcHead,
      Ref = SqlFunctions.StringConvert((double)le.FreqId),
      PmntDtTm = lp.PmntDtTm,
      Amount = lp.Amount,
      IsDiscard = lp.IsDiscarded,
      DiscardRemarks = lp.DiscardRemarks
  }).DefaultIfEmpty(ObjNull));

  List<ObjGetAllPendingPmntDetails> test = testquery.ToList();

This query gives the following Exception Message -

Unable to create a constant value of type CashVitae.ObjGetAllPendingPmntDetails. Only primitive types or enumeration types are supported in this context.

I got this Exception after I added the SQL function statement to convert le.FreqId which is a byte to a string as ToString() is not recognized in the LINQ Expression Store.

ObjGetAllPendingPmntDetails is a partial class in my model which is added as it is used too many times in the code to bind data to tables. It has both IDs as long, 'Amount' as decimal, PmntDtTm as Datetime,IsDiscard as bool and remaining all are string including 'Ref'.

I get no results as currently no data satisfies the condition. While trying to handle null, I added DefaultIfEmpty(ObjNull) and ObjNull has all properties initialized as follows.

ObjGetAllPendingPmntDetails ObjNull = new ObjGetAllPendingPmntDetails()
{ Id = 0, Table = "-", loanEMIId = 0, Name = "-", Ref = "-",
  PmntDtTm = Convert.ToDateTime("01-01-1900"),
  Amount = 0, IsDiscard = false, DiscardRemarks = "" };

I need this query to work fine as it has Union() called on it with 5 other queries. All returning the same ObjGetAllPendingPmntDetails columns. But there is some problem as this query has no data satisfying the conditions and the Exception Shared Above.

Any suggestions are appreciated as I am unable to understand the root cause of the problem.

Answer 1

@AndrewCoonce is right, the .DefaultIfEmpty(ObjNull) is the culprit here. Entity Framework turns DefaultIfEmpty into something like...

CASE WHEN ([Project1].[C1] IS NULL) THEN @param ELSE [Project1].[Value] END AS [C1]

...but there's no way to coerce an instance of ObjGetAllPendingPmntDetails into something that can take the place of @param, so you get an exception.

If you move the DefaultIfEmpty call to after the ToList it should work correctly (although you'll need to call ToList again after that if you really want a concrete list instance).