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 Notifier
like 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?.
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);
}