Entity Framework分组后OrderBy报错：无法翻译查询如何解决？

模型定义 #

public class ProductModel{
    public string? Id { get; set; }
    public ProductItem ProductGroep { get; set; }
    public ProductItem Product { get; set; }
    public ProductItem ProductDekking { get; set; }
    public List<ProductItem> Dekkingen { get; set; }
}

public class ProductItem {
    public int? Id { get; set; }
    public string? Naam { get; set; }
    public int? ProductId { get; set; }
    public string? ProductIdNaamCombo { get; set; }
}

原始查询代码 #

var query = (from rdk in dbContext.tblRollsDekkingens
    join pvp in dbContext.tblPremievergelijkProductens on rdk.rdk_id equals pvp.pvp_rdk_id
    join rvp in dbContext.tblRolVerzamelingProductens on pvp.pvp_id equals rvp.rvp_pvp_id
    join rpn in dbContext.tblRollsProductens on rdk.rdk_rpn_id equals rpn.rpn_id
    join cow1 in dbContext.tblCodewaardes on rvp.rvp_rolverzamelingproducten_cow_id equals cow1.cow_id
    join cow2 in dbContext.tblCodewaardes on pvp.pvp_dekking_cow_id equals cow2.cow_id
    select new ProductModel()
    {
        Id = cow1.cow_id.ToString() + "_" + rpn.rpn_id.ToString() + "_" + rdk.rdk_id.ToString(),
        ProductGroep = new ProductItem()
        {
            Id = cow1.cow_id,
            ProductId = 0,
            Naam = cow1.cow_waarde
        },
        Product = new ProductItem()
        {
            Id = rpn.rpn_id,
            ProductId = rpn.rpn_product_id,
            Naam = rpn.rpn_product_name,
            ProductIdNaamCombo = rpn.rpn_product_id.ToString() + " - " + rpn.rpn_product_name
        },
        ProductDekking = new ProductItem()
        {
            Id = rdk.rdk_id,
            ProductId = rdk.rdk_product_id,
            Naam = rdk.rdk_product_name,
            ProductIdNaamCombo = rdk.rdk_product_id.ToString() + " - " + rdk.rdk_product_name
        },
        Dekkingen = (from pvp_dek in dbContext.tblPremievergelijkProductens
            join cow_dek in dbContext.tblCodewaardes on pvp_dek.pvp_dekking_cow_id equals cow_dek.cow_id
            join rdk_dek in dbContext.tblRollsDekkingens on pvp_dek.pvp_rdk_id equals rdk_dek.rdk_id
            join rvp_dek in dbContext.tblRolVerzamelingProductens on pvp_dek.pvp_id equals rvp_dek.rvp_pvp_id
            where rdk_dek.rdk_id == rdk.rdk_id && rdk_dek.rdk_rpn_id == rpn.rpn_id && rvp_dek.rvp_rolverzamelingproducten_cow_id == cow1.cow_id
            select new ProductItem
            {
                Id = cow_dek.cow_id,
                Naam = cow_dek.cow_waarde
            }
        ).ToList()
    }
);

问题场景 #

上述查询会返回重复的ProductModel记录：同一ProductGroep、Product、ProductDekking组合对应多条仅Dekkingen列表不同的记录。尝试用以下代码分组去重后，执行排序和分页时触发"Could not translate"错误：

// 分组去重尝试
query = query.GroupBy(m => new { pg = m.ProductGroep, p = m.Product, pd = m.ProductDekking }).Select(m => m.First());
// 添加排序
query = query.OrderBy(p => p.ProductGroep.Naam);
// 分页查询
producten = await query.Skip(model.FirstItem).Take(model.RowCount).ToListAsync();

原因分析 #

1. EF Core无法将基于引用类型（如ProductItem）的GroupBy逻辑翻译成SQL，分组键必须是可被数据库识别的基础值类型字段。
2. 分组后直接在IQueryable上执行OrderBy，EF Core无法解析分组后的复杂对象结构，导致翻译失败。

解决方案 #

方案一：基于基础字段分组（推荐） #

直接用ProductGroep、Product、ProductDekking的唯一标识字段作为分组键，确保EF Core能正确生成SQL，同时合并分组内的Dekkingen列表：

var groupedQuery = query
    .GroupBy(m => new 
    {
        // 分组核心：用唯一标识字段替代整个引用类型
        ProductGroepId = m.ProductGroep.Id,
        ProductGroepNaam = m.ProductGroep.Naam,
        ProductId = m.Product.Id,
        ProductNaam = m.Product.Naam,
        ProductDekkingId = m.ProductDekking.Id,
        ProductDekkingNaam = m.ProductDekking.Naam,
        // 保留模型所需的其他字段
        ModelId = m.Id,
        Product_ProductId = m.Product.ProductId,
        Product_ProductIdNaamCombo = m.Product.ProductIdNaamCombo,
        ProductDekking_ProductId = m.ProductDekking.ProductId,
        ProductDekking_ProductIdNaamCombo = m.ProductDekking.ProductIdNaamCombo
    })
    .Select(g => new ProductModel
    {
        Id = g.Key.ModelId,
        ProductGroep = new ProductItem
        {
            Id = g.Key.ProductGroepId,
            ProductId = 0,
            Naam = g.Key.ProductGroepNaam
        },
        Product = new ProductItem
        {
            Id = g.Key.ProductId,
            ProductId = g.Key.Product_ProductId,
            Naam = g.Key.ProductNaam,
            ProductIdNaamCombo = g.Key.Product_ProductIdNaamCombo
        },
        ProductDekking = new ProductItem
        {
            Id = g.Key.ProductDekkingId,
            ProductId = g.Key.ProductDekking_ProductId,
            Naam = g.Key.ProductDekkingNaam,
            ProductIdNaamCombo = g.Key.ProductDekking_ProductIdNaamCombo
        },
        // 合并所有分组内的Dekkingen，并按Id去重
        Dekkingen = g.SelectMany(m => m.Dekkingen)
                     .DistinctBy(d => d.Id)
                     .ToList()
    });

// 正常执行排序和分页
var producten = await groupedQuery
    .OrderBy(p => p.ProductGroep.Naam)
    .Skip(model.FirstItem)
    .Take(model.RowCount)
    .ToListAsync();

方案二：先加载到内存再处理（适合小数据量） #

如果分组逻辑复杂，可先将去重后的基础数据加载到内存，再合并Dekkingen并排序：

// 先获取去重的基础模型（仅核心字段），加载到内存
var distinctBaseModels = await query
    .Select(m => new 
    {
        m.Id,
        m.ProductGroep,
        m.Product,
        m.ProductDekking
    })
    .DistinctBy(m => new { m.ProductGroep.Id, m.Product.Id, m.ProductDekking.Id })
    .ToListAsync();

// 合并Dekkingen并排序分页
var productModels = distinctBaseModels
    .Select(baseModel => new ProductModel
    {
        Id = baseModel.Id,
        ProductGroep = baseModel.ProductGroep,
        Product = baseModel.Product,
        ProductDekking = baseModel.ProductDekking,
        Dekkingen = query
            .Where(m => m.ProductGroep.Id == baseModel.ProductGroep.Id 
                     && m.Product.Id == baseModel.Product.Id 
                     && m.ProductDekking.Id == baseModel.ProductDekking.Id)
            .SelectMany(m => m.Dekkingen)
            .DistinctBy(d => d.Id)
            .ToList()
    })
    .OrderBy(p => p.ProductGroep.Naam)
    .Skip(model.FirstItem)
    .Take(model.RowCount)
    .ToList();

关键提示 #

• DistinctBy是.NET 6及以上版本的方法，用于按指定字段去重，无需重写ProductItem的Equals和GetHashCode方法。
• 方案一优先选择，因为大部分逻辑在数据库层面执行，性能更优；方案二仅适合数据量较小的场景，避免内存压力过大。

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