Linq - EntityFramework NotSupportedException

Question

I have a query that looks like this:

var caseList = (from x in context.Cases
         where allowedCaseIds.Contains(x => x.CaseId)
         select new Case {
            CaseId = x.CaseId,
            NotifierId = x.NotifierId,
            Notifier = x.NotifierId.HasValue ? new Notifier { Name = x.Notifier.Name } : null // This line throws exception
         }).ToList();

A Case class can have 0..1 Notifier

The query above will result in the following System.NotSupportedException:

Unable to create a null constant value of type 'Models.Notifier'. Only entity types, enumeration types or primitive types are supported in this context.

At the moment the only workaround I found is to loop the query result afterwards and manually populate Notifierlike this:

foreach (var c in caseList.Where(x => x.NotifierId.HasValue)
{
    c.Notifier = (from x in context.Notifiers 
                 where x.CaseId == c.CaseId
                 select new Notifier {
                     Name = x.Name
                 }).FirstOrDefault();
}

But I really don't want to do this because in my actual scenario it would generate hundreds of additional queries.

Is there any possible solution for a situation like this?.

Answer 1

You could try writing your query as a chain of function calls rather than a query expression, then put an .AsEnumerable() in between:

var caseList = context.Clases
    .Where(x => allowedCaseIds.Contains(x.CaseId))
    .AsEnumerable()                                 // Switch context
    .Select(x => new Case() {
        CaseId = x.CaseId,
        NotifierId = x.NotifierId,
        Notifier = x.NotifierId.HasValue
            ? new Notifier() { Name = x.Notifier.Name }
            : null
    })
    .ToList();

This will cause EF to generate an SQL query only up to the point where you put the .AsEnumerable(), further down the road, LINQ to Objects will do all the work. This has the advantage that you can use code that cannot be translated to SQL and should not require a lot of changes to your existing code base (unless you're using a lot of let expressions...)

Answer 2

I think you need to do that in two steps. First you can fetch only the data what you need with an anonymous type in a single query:

var caseList = (from x in context.Cases
     where allowedCaseIds.Contains(x => x.CaseId)
     select new {
        CaseId = x.CaseId,
        NotifierId = x.NotifierId,
        NotifierName = x.Notifier.Name
     }).ToList();

After that, you can work in memory:

List<Case> cases = new List<Case>();
foreach (var c in caseList)
{
    var case = new Case();
    case.CaseId = c.CaseId;
    case.NotifierId = c.NotifierId;
    case.NotifierName = c.NotifierId.HasValue ? c.NotifierName : null;
    cases.Add(case);
}