EF Framework迁移EF Core的LINQ查询转换错误及优化求助

问题场景 #

将EF Framework代码迁移至EF Core时，触发InvalidOperationException错误：指定LINQ表达式无法被转换，需重写查询或显式启用客户端评估。

原EF Framework查询代码 #

(from ij in ImportJobs
 join id in ImportDefinitions
     on ij.ImportDefinitionId equals id.ImportDefinitionId
 join it in ImportTypes
     on id.ImportTypeId equals it.ImportTypeId
 join ijp in ImportJobParameters
     on ij.ImportJobId equals ijp.ImportJobId into ijpGroup
 where ij.JobQueuedTimeUtc >= DateTime.Now.AddDays(-30)
 orderby ij.JobQueuedTimeUtc descending
 select
     new
     {
         ImportDefinition = id,
         ImportType = it,
         LastImportJob = ij,
         LastImportJobParameters = ijpGroup
     }).ToList()

修改后的尝试代码（存在空元素问题） #

修改后解决了转换错误，但原本应返回空集合的LastImportJobParameters会返回包含1个null元素的集合，需手动过滤：

(from ij in ImportJobs
 join id in ImportDefinitions
     on ij.ImportDefinitionId equals id.ImportDefinitionId
 join it in ImportTypes
     on id.ImportTypeId equals it.ImportTypeId
 from ijp in ImportJobParameters.Where(ijp => ij.ImportJobId == ijp.ImportJobId).DefaultIfEmpty()                 
 where ij.JobQueuedTimeUtc >= DateTime.Now.AddDays(-60)
 orderby ij.JobQueuedTimeUtc descending
 select
     new
     {
         ImportDefinition = id,
         ImportType = it,
         LastImportJob = ij,
         LastImportJobParameter = ijp
     }).ToList()
     .GroupBy(i => new { i.ImportDefinition, i.ImportType, i.LastImportJob })
   .Select(i => new { i.Key.ImportDefinition, i.Key.ImportType, i.Key.LastImportJob, LastImportJobParameters = i.Select(s => s.LastImportJobParameter) })

简化后的实体类定义 #

public class ImportJob
{
    [Key]
    public int? ImportJobId { get; set; }
    
    [Required]
    public Int16? ImportDefinitionId { get; set; }

    [NotMapped]
    public ImportDefinition ImportDefinition { get; set; }

    public DateTime? JobQueuedTimeUtc { get; set; }

    [NotMapped]
    public List<ImportJobParameter> ImportJobParameters { get; set; }
 
}   

public class ImportJobParameter
{
    [Key]
    public int? ImportJobParameterId { get; set; }

    [Required]
    public int? ImportJobId { get; set; }

    [Required]
    public short? ImportParameterId { get; set; }

    public string ParameterName { get; set; }

    public string ParameterValue { get; set; }

}   

public class ImportDefinition
{
    [Key]
    public Int16? ImportDefinitionId
    {
        get;
        set;
    }
    [Required]
    [StringLength(255)]
    public string Name
    {
        get;
        set;
    }

    public ImportType ImportType { get; set; }

    [Required]
    public Int16? ImportTypeId { get; set; }
}

public class ImportType
{
    [Key]
    public Int16? ImportTypeId { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }
}

解决方案 #

方案1：使用导航属性（推荐） #

移除ImportJob类中ImportJobParameters的[NotMapped]特性，并在DbContext中配置ImportJob与ImportJobParameter的一对多关系，之后直接通过导航属性获取参数集合，EF Core可正确转换：

(from ij in ImportJobs
 join id in ImportDefinitions on ij.ImportDefinitionId equals id.ImportDefinitionId
 join it in ImportTypes on id.ImportTypeId equals it.ImportTypeId
 where ij.JobQueuedTimeUtc >= DateTime.Now.AddDays(-30)
 orderby ij.JobQueuedTimeUtc descending
 select new
 {
     ImportDefinition = id,
     ImportType = it,
     LastImportJob = ij,
     LastImportJobParameters = ij.ImportJobParameters
 }).ToList()

方案2：数据库端分组关联 #

若无法使用导航属性，可先在数据库端对参数分组，再左关联主查询，确保无参数时返回空集合：

(from ij in ImportJobs
 join id in ImportDefinitions on ij.ImportDefinitionId equals id.ImportDefinitionId
 join it in ImportTypes on id.ImportTypeId equals it.ImportTypeId
 join paramGroup in (
     from ijp in ImportJobParameters
     group ijp by ijp.ImportJobId into g
     select new { ImportJobId = g.Key, Parameters = g.ToList() }
 ) on ij.ImportJobId equals paramGroup.ImportJobId into paramGroupJoin
 from pg in paramGroupJoin.DefaultIfEmpty()
 where ij.JobQueuedTimeUtc >= DateTime.Now.AddDays(-30)
 orderby ij.JobQueuedTimeUtc descending
 select new
 {
     ImportDefinition = id,
     ImportType = it,
     LastImportJob = ij,
     LastImportJobParameters = pg?.Parameters ?? new List<ImportJobParameter>()
 }).ToList()

内容的提问来源于stack exchange，提问作者gumby