Linq - EntityFramework NotSupportedException

1
votes

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

2
c#entity-frameworklinqentity-framework-6
What really are you trying to achieve with the projection? Isn't Case an entity and x.Notifier a navigation property? I mean, remove the whole custom select and use context.Cases.Include(c => c.Notifier) - Ivan Stoev
I'm printing a list of Cases in which I need to display the Notifier name (among many other things). Yes, Notifier is a navigation property with 0..1 multiplicity - Javier
So again, why not just including it then? - Ivan Stoev
Sorry, I got you wrong. Case is part of my viewmodel - Javier
I see. And Notifier is not the same type as x.Notifier. - Ivan Stoev

2 Answers

4
votes

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);
}
0
votes

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