EF中如何将多类实体合并为IQueryable且避免查询物化？

核心问题分析 #

你遇到的System.InvalidOperationException错误，本质是两个原因导致：

1. 投影中调用了.ToList()强制客户端物化集合，EF无法将后续的Union操作翻译为SQL
2. 先执行Select投影再做集合操作，EF无法合并客户端投影后的表达式树到数据库查询中

修复步骤及代码示例 #

1. 调整DTO集合属性类型

将CombinedCalculationDto中的Owners属性改为IEnumerable<OwnerDto>（避免强制调用.ToList()触发客户端求值）：

public class CombinedCalculationDto
{
    public int Id { get; set; }
    public string SubmitterName { get; set; }
    public int SubmitterId { get; set; }
    public IEnumerable<OwnerDto> Owners { get; set; }
    // 其他公共属性
}

2. 重构仓储类查询逻辑

移除冗余的Include（使用Select投影时，EF会自动生成关联查询，Include会被忽略），直接在Select中构造DTO，确保所有操作都能被EF翻译为SQL：

public class CombinedCalculationRepository
{
    private readonly DbSet<PlannedCalculation> _plannedCalculations;
    private readonly DbSet<UpdatingCalculation> _updatingCalculations;
    private readonly DbSet<EqualCalculation> _equalCalculations;
    
    public CombinedCalculationRepository(MyDbContext context)
    {
        _plannedCalculations = context.PlannedCalculations;
        _updatingCalculations = context.UpdatingCalculations;
        _equalCalculations = context.EqualCalculations;
    }

    public IQueryable<CombinedCalculationDto> GetCombinedCalculations() 
    {
        // 分别投影各实体到统一DTO，再执行Union
        var plannedQuery = _plannedCalculations.Select(p => new CombinedCalculationDto
        {
            Id = p.Id,
            SubmitterName = p.Submitter.Name,
            SubmitterId = p.Submitter.Id,
            Owners = p.Owners.Select(o => new OwnerDto
            {
                Id = o.Id,
                Name = o.Name
            }),
            // 映射其他公共属性
        });

        var updatingQuery = _updatingCalculations.Select(u => new CombinedCalculationDto
        {
            Id = u.Id,
            SubmitterName = u.Submitter.Name,
            SubmitterId = u.Submitter.Id,
            Owners = u.Owners.Select(o => new OwnerDto
            {
                Id = o.Id,
                Name = o.Name
            }),
            // 映射其他公共属性
        });

        var equalQuery = _equalCalculations.Select(e => new CombinedCalculationDto
        {
            Id = e.Id,
            SubmitterName = e.Submitter.Name,
            SubmitterId = e.Submitter.Id,
            Owners = e.Owners.Select(o => new OwnerDto
            {
                Id = o.Id,
                Name = o.Name
            }),
            // 映射其他公共属性
        });

        // 若不需要去重，可使用Concat替代Union（性能更优）
        return plannedQuery.Union(updatingQuery).Union(equalQuery);
    }
}

3. 优化服务层调用逻辑

保留IQueryable直到最后执行.ToListAsync()，确保筛选、排序、分页等操作都在数据库端完成：

public class CalculationService
{
    private readonly IPlannedCalculationRepository _plannedCalculationRepository;
    private readonly IUpdatingCalculationRepository _updatingCalculationRepository;
    private readonly IEqualCalculationRepository _equalCalculationRepository;
    private readonly ICombinedCalculationRepository _combinedCalculationRepository;

    public async Task<List<CalculationDetailDto>> GetCalculationsAsync(CalculationType calculationType)
    {
        switch (calculationType)
        {
            case CalculationType.Planned:
                var planned = await _plannedCalculationRepository.GetCalculations().ToListAsync();
                return MapToCalculationDetailDto(planned);
            case CalculationType.Updating:
                var updating = await _updatingCalculationRepository.GetCalculations().ToListAsync();
                return MapToCalculationDetailDto(updating);
            case CalculationType.Equal:
                var equal = await _equalCalculationRepository.GetCalculations().ToListAsync();
                return MapToCalculationDetailDto(equal);
            default:
                // 此处可添加筛选、排序、分页逻辑，全部在数据库端执行
                var combined = await _combinedCalculationRepository.GetCombinedCalculations()
                    .Where(dto => dto.SubmitterName.Contains("关键词"))
                    .OrderBy(dto => dto.Id)
                    .Skip(10)
                    .Take(10)
                    .ToListAsync();
                return MapToCalculationDetailDto(combined);
        }
    }

    // 手动映射或使用AutoMapper实现DTO转换
    private List<CalculationDetailDto> MapToCalculationDetailDto<T>(List<T> source)
    {
        // 转换逻辑
    }
}

关键注意事项 #

• 避免客户端物化操作：投影中不要使用.ToList()、.ToArray()等方法，保持集合属性为IEnumerable<T>，让EF能翻译为SQL关联查询
• 移除冗余Include：使用Select投影时，EF会自动处理关联数据加载，Include属于多余操作且可能干扰查询翻译
• 优先使用Concat：如果不需要去重，用Concat替代Union，减少数据库端的去重计算开销

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