c# - Linq - EntityFramework NotSupportedException

Question

我有一个如下所示的查询:

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();

Case 类可以有 0..1 个 Notifier

上面的查询将导致以下 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.

目前我发现的唯一解决方法是之后循环查询结果并手动填充 Notifier，如下所示:

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();
}

但我真的不想这样做，因为在我的实际场景中它会生成数百个额外的查询。

对于这种情况，有什么可能的解决方案吗？。

Answer 1

我认为您需要分两步完成。首先，您可以在单个查询中使用匿名类型仅获取您需要的数据:

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();

在那之后，你可以在内存中工作:

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