EF Core对IQueryable执行Union报错：无法转换客户端投影后的集合操作

错误原因很明确：EF Core 不支持在客户端投影（即Select创建DTO之后）执行集合操作（如Union），因为此时查询已经被转换为客户端类型，无法再映射到SQL的Union语句。

最优解决方案 #

核心思路是将Union操作提前到投影（Select）之前：先把两个查询的结果统一为结构完全一致的匿名类型，执行Union后再投影成目标DTO。这样所有操作都能被EF Core转换为SQL语句在数据库端执行，避免客户端内存处理。

修改后的代码示例

// 第一步：将dataSet1投影为匿名类型，保留所有需要的字段
var dataSet1 = from f in _marketTransaction.GetDbSet()
    join c in _commodityRepository.GetDbSet() on f.CommodityId equals c.Id
    join p in _productRepository.GetDbSet() on c.ProductId equals p.Id
    join h in _hedgeAccountRepository.GetDbSet() on f.MarketAccount equals h.Account into gha
    from account in gha.DefaultIfEmpty()
    join con in _contractRepository.GetDbSet() on f.ContractId equals con.Id into contract
    from ct in contract.DefaultIfEmpty()
    join off in _offerRepository.GetDbSet() on f.OfferId equals off.Id into offer
    from of in offer.DefaultIfEmpty()
    join cusForCon in _customerRepository.GetDbSet() on ct.CustomerId equals cusForCon.Id into customerForContract
    from customerForCon in customerForContract.DefaultIfEmpty()
    join cusForOff in _customerRepository.GetDbSet() on of.CustomerId equals cusForOff.Id into customerForOffer
    from customerForOff in customerForOffer.DefaultIfEmpty()
    where f.IsActive == true && employeeLocations.Any(em => em.LocationId == ct.DeliveryLocationId || em.LocationId == of.DeliveryLocationId)
    select new 
    {
        Id = f.Id,
        FirstName = customerForCon.FirstName,
        LastName = customerForCon.LastName,
        Number = customerForCon.Number,
        CreatedOn = f.CreatedOn,
        UpdatedOn = f.UpdatedOn,
        IsTransaction = true,
        FuturesPrice = f.FuturesPrice,
        IsSell = f.IsSell,
        MarketAccount = f.MarketAccount,
        WasAcknowledge = f.WasAcknowledge,
        PassFill = f.PassFill,
        IsGtc = f.IsGtc,
        Expiration = f.Expiration,
        Lots = f.Lots,
        WorkingLots = f.WorkingLots,
        ProductId = c.ProductId,
        ProductCode = p.Code,
        MarketPrice = f.MarketPrice,
        FuturesMonth = f.FuturesMonth,
        MarketId = f.MarketId,
        AccountName = account.Name,
        InternalCode = f.InternalCode,
        Source = f.Source switch
        {
            EMarketTransactionSource.Contract => EMarketTransactionSource.Contract,
            EMarketTransactionSource.Accumulation => EMarketTransactionSource.Accumulation,
            EMarketTransactionSource.Offer => EMarketTransactionSource.Offer,
            _ => EMarketTransactionSource.ExternalFill
        },
        Type = f.Type == EMarketTransactionType.Market ? EMarketTransactionType.Market : EMarketTransactionType.Limit,
        Event = f.Event switch
        {
            ETransactionEvent.Creation => ETransactionEvent.Creation,
            ETransactionEvent.Edition => ETransactionEvent.Edition,
            ETransactionEvent.Cancelation => ETransactionEvent.Cancelation,
            ETransactionEvent.Roll => ETransactionEvent.Roll,
            ETransactionEvent.Book => ETransactionEvent.Book,
            ETransactionEvent.RollBack => ETransactionEvent.RollBack,
            ETransactionEvent.PartiallyFilled => ETransactionEvent.PartiallyFilled,
            ETransactionEvent.Filled => ETransactionEvent.Filled,
            _ => ETransactionEvent.Completed
        },
        State = f.State.Value switch
        {
            EMarketTransactionState.Ready => EMarketTransactionState.Ready,
            EMarketTransactionState.Denied => EMarketTransactionState.Denied,
            EMarketTransactionState.Pending => EMarketTransactionState.Pending,
            EMarketTransactionState.Working => EMarketTransactionState.Working,
            EMarketTransactionState.PartiallyFilled => EMarketTransactionState.PartiallyFilled,
            EMarketTransactionState.Filled => EMarketTransactionState.Filled,
            EMarketTransactionState.Canceled => EMarketTransactionState.Canceled,
            EMarketTransactionState.Rejected => EMarketTransactionState.Rejected,
            _ => EMarketTransactionState.Expired
        }
    };

// 第二步：将dataSet2投影为与dataSet1完全一致的匿名类型（字段名、类型必须匹配）
var dataSet2 = from f in _offerMonitoringRepository.GetDbSet()
    join o in _offerRepository.GetDbSet() on f.OfferId equals o.Id
    join c in _commodityRepository.GetDbSet() on o.CommodityId equals c.Id
    join p in _productRepository.GetDbSet() on c.ProductId equals p.Id
    join cusForOf in _customerRepository.GetDbSet() on o.CustomerId equals cusForOf.Id
    where f.Action.Value == EOfferMonitorAction.Add && employeeLocations.Any(el => o.LocationId == el.LocationId)
    select new 
    {
        Id = f.Id,
        FirstName = cusForOf.FirstName,
        LastName = cusForOf.LastName,
        Number = cusForOf.Number,
        CreatedOn = f.CreatedOn,
        UpdatedOn = f.UpdatedOn,
        IsTransaction = false,
        FuturesPrice = o.FuturesPrice ?? 0,
        IsSell = !o.IsSell,
        MarketAccount = default(int), // 注意：类型必须与dataSet1的MarketAccount一致，若为string则改为""
        WasAcknowledge = false,
        PassFill = false,
        IsGtc = o.Gtc,
        Expiration = o.Expiration,
        Lots = (int)(o.Quantity / c.LotFactor),
        WorkingLots = 0,
        ProductId = c.ProductId,
        ProductCode = p.Code,
        MarketPrice = o.FreightPrice,
        FuturesMonth = o.FuturesMonth,
        MarketId = "", // 类型必须与dataSet1的MarketId一致
        AccountName = "", // 类型必须与dataSet1的AccountName一致
        InternalCode = o.InternalCode,
        Source = EMarketTransactionSource.Offer,
        Type = EMarketTransactionType.Limit,
        Event = f.Action.Value switch
        {
            EOfferMonitorAction.Cancel => ETransactionEvent.Cancelation,
            EOfferMonitorAction.Add => ETransactionEvent.Creation,
            _ => ETransactionEvent.Edition
        },
        State = EMarketTransactionState.Pending
    };

// 第三步：先执行Union，再投影为目标DTO
var query = dataSet1.Union(dataSet2)
    .Select(x => new ReviewAndReleaseDto(
        x.Id, 
        x.FirstName, 
        x.LastName, 
        x.Number, 
        x.CreatedOn, 
        x.UpdatedOn, 
        x.IsTransaction, 
        x.FuturesPrice, 
        x.IsSell, 
        x.MarketAccount,
        x.WasAcknowledge, 
        x.PassFill, 
        x.IsGtc, 
        x.Expiration, 
        x.Lots, 
        x.WorkingLots, 
        x.ProductId, 
        x.ProductCode, 
        x.MarketPrice, 
        x.FuturesMonth,
        x.MarketId, 
        x.AccountName, 
        x.InternalCode, 
        x.Source, 
        x.Type, 
        x.Event, 
        x.State
    ));

关键注意事项 #

1. 匿名类型结构必须完全一致：两个匿名类型的字段名、数据类型必须一一对应，否则Union操作会失败。
2. 避免客户端提前加载：不要在Union前调用ToList()或AsEnumerable()，否则会将数据加载到内存处理，仅适合小数据量场景。
3. 简化条件判断：使用C# 8.0+的switch表达式替代嵌套三元运算符，代码更清晰且不影响EF Core的SQL转换。

备选方案（仅适合小数据量） #

如果数据量很小，可以先将两个查询加载到内存再执行Union，但会牺牲性能：

var list1 = dataSet1.ToList();
var list2 = dataSet2.ToList();
var query = list1.Union(list2).AsQueryable();

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