如何避免LINQ查询手动内联，保留表达式并转换为SQL

我在LINQ中进行投影转换为DTO，目标是让转换逻辑直接在SQL中执行。内联编写映射代码时可正常生成SQL投影，仅查询Tool所需列；但将映射放入静态方法复用会导致SQL查询全字段并在内存中映射；使用Expression<Func<Tool, ToolDto>>复用则在HotChocolate GraphQL查询中报错。请问如何在复用映射代码的同时保持SQL端投影？

内联可行代码示例 #

return workHeader.Select(x => new WorkHeaderDto
{
    Id = x.Id,
    StartDate = x.Works.Min(w => w.StartDate),
    EndDate = x.Works.Min(w => w.EndDate),
    Parcelles = x.Works.SelectMany(w => w.Parcelles).Distinct(),
    Tools = x.Works.SelectMany(w => w.Tools).Select(t => t.Tool).Distinct().OrderBy(t => t.ToolId).Select(t => new ToolDto
    {
        RankNumber = t.RankNumber,
        Comments = t.Comments,
        OrderNumber = t.OrderNumber,
        Garage = t.Garage,
        Maintained = t.Maintained,
        OI = t.OI,
    })
});

静态方法复用示例（内存映射） #

return workHeader.Select(x => new WorkHeaderDto
{
    Id = x.Id,
    StartDate = x.Works.Min(w => w.StartDate),
    EndDate = x.Works.Min(w => w.EndDate),
    Parcelles = x.Works.SelectMany(w => w.Parcelles).Distinct(),
    Tools = x.Works.SelectMany(w => w.Tools).Select(t => t.Tool).Distinct().OrderBy(t => t.ToolId).AsQueryable().Select(t => DtoMapper.ToDto(t))
});

表达式复用示例（报错） #

static Expression<Func<Tool, ToolDto>> ConvertExpression = t1 => new ToolDto
{
    RankNumber = t.RankNumber,
    Comments = t.Comments,
    OrderNumber = t.OrderNumber,
    Garage = t.Garage,
    Maintained = t.Maintained,
    OI = t.OI,
};

return workHeader.Select(x => new WorkHeaderDto
{
    Id = x.Id,
    StartDate = x.Works.Min(w => w.StartDate),
    EndDate = x.Works.Min(w => w.EndDate),
    Parcelles = x.Works.SelectMany(w => w.Parcelles).Distinct(),
    Tools = x.Works.SelectMany(w => w.Tools).Select(t => t.Tool).Distinct().OrderBy(t => t.ToolId).AsQueryable().Select(ConvertExpression)
});

解决方案 #

要复用映射表达式并确保EF Core能解析生成SQL投影，同时适配HotChocolate的查询处理，可按以下步骤调整：

1. 修正表达式参数名冲突 #

原表达式中参数定义为t1，但内部使用t引用属性，属于笔误，修正后确保参数一致：

static Expression<Func<Tool, ToolDto>> ConvertExpression = t => new ToolDto
{
    RankNumber = t.RankNumber,
    Comments = t.Comments,
    OrderNumber = t.OrderNumber,
    Garage = t.Garage,
    Maintained = t.Maintained,
    OI = t.OI,
};

2. 移除多余的AsQueryable()调用 #

嵌套查询中的AsQueryable()会干扰EF Core的表达式树解析，直接移除即可：

return workHeader.Select(x => new WorkHeaderDto
{
    Id = x.Id,
    StartDate = x.Works.Min(w => w.StartDate),
    EndDate = x.Works.Min(w => w.EndDate),
    Parcelles = x.Works.SelectMany(w => w.Parcelles).Distinct(),
    Tools = x.Works.SelectMany(w => w.Tools)
        .Select(t => t.Tool)
        .Distinct()
        .OrderBy(t => t.ToolId)
        .Select(ConvertExpression)
});

3. 封装表达式为可复用方法（适配HotChocolate） #

如果HotChocolate仍无法处理静态表达式变量，可将映射逻辑封装为返回表达式的静态方法，提升兼容性：

public static class DtoMapper
{
    public static Expression<Func<Tool, ToolDto>> ToToolDto()
    {
        return t => new ToolDto
        {
            RankNumber = t.RankNumber,
            Comments = t.Comments,
            OrderNumber = t.OrderNumber,
            Garage = t.Garage,
            Maintained = t.Maintained,
            OI = t.OI,
        };
    }
}

查询中调用方式：

Tools = x.Works.SelectMany(w => w.Tools)
    .Select(t => t.Tool)
    .Distinct()
    .OrderBy(t => t.ToolId)
    .Select(DtoMapper.ToToolDto())

4. 确保映射逻辑可被EF Core翻译 #

检查ToolDto与Tool的属性类型是否完全匹配，避免在映射中使用EF Core无法翻译的自定义方法或复杂逻辑，所有投影操作必须是EF Core支持的标准表达式节点。

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