EF Core如何通过单查询实现Point与Prop表的关联匹配逻辑

核心思路 #

通过关联+分组/窗口函数排序取首条的方式，在单查询内满足三个规则：过滤无对应Prop的Point、单个Prop直接合并、多个Prop优先匹配Type再任选。

假设实体结构 #

先明确基础实体类（可根据实际场景调整属性）：

public class Point
{
    public int Id { get; set; }
    public string Type { get; set; }
    // 其他业务属性
}

public class Prop
{
    public int Id { get; set; }
    public int PointId { get; set; }
    public string Foo { get; set; }
    // 其他业务属性
}

public class Result
{
    public int PointId { get; set; }
    public string PointType { get; set; }
    public int PropId { get; set; }
    public string PropFoo { get; set; }
    // 合并后的其他属性
}

方案1：GroupBy 分组取首条 #

适配所有EF Core版本，逻辑直观易懂：

var results = context.Points
    // 内连接自动过滤无对应Prop的Point（满足规则1）
    .Join(context.Props,
        point => point.Id,
        prop => prop.PointId,
        (point, prop) => new { Point = point, Prop = prop })
    // 按Point的ID分组，聚合该Point的所有关联Prop记录
    .GroupBy(joinObj => joinObj.Point.Id)
    // 组内排序：优先保留Foo等于Point.Type的记录，取每组第一条
    .Select(group => group
        .OrderByDescending(item => item.Prop.Foo == item.Point.Type)
        .FirstOrDefault())
    // 映射为Result实体
    .Select(matched => new Result
    {
        PointId = matched.Point.Id,
        PointType = matched.Point.Type,
        PropId = matched.Prop.Id,
        PropFoo = matched.Prop.Foo
        // 补充其他属性映射
    })
    .ToList();

方案2：窗口函数（推荐，性能更优） #

EF Core 3.0及以上版本支持窗口函数，由数据库层面直接处理排序分组，执行效率更高：

var results = context.Points
    .Join(context.Props, p => p.Id, pr => pr.PointId, (p, pr) => new { p, pr })
    // 给每个Point的关联Prop打行号：按Point分组，优先匹配Type的记录排第一
    .Select(joinObj => new
    {
        joinObj.p,
        joinObj.pr,
        RowNum = EF.Functions.RowNumber()
            .Over(PartitionBy(joinObj.p.Id)
                  .OrderByDescending(item => item.pr.Foo == item.p.Type))
    })
    // 只保留每组的第一条记录
    .Where(item => item.RowNum == 1)
    // 映射为Result实体
    .Select(matched => new Result
    {
        PointId = matched.p.Id,
        PointType = matched.p.Type,
        PropId = matched.pr.Id,
        PropFoo = matched.pr.Foo
        // 补充其他属性映射
    })
    .ToList();

补充说明 #

• 如果同个Point下存在多条Foo等于Point.Type的Prop记录，可在排序条件后追加ThenBy(pr => pr.Id)（或其他业务字段），确保取固定顺序的记录。
• 建议给Point.Id与Prop.PointId配置外键索引，进一步提升查询效率。

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