如何用Linq和EF Core获取每个报表的最高版本及对应属性？

需求说明 #

已通过SQL实现获取每个报表的最高版本及关联属性的查询，但不清楚如何用LINQ实现。需求为：输出每个报表的最高版本及其关联属性，每个报表存在一个或多个版本，仅保留各报表的最高版本数据。

数据库结构 #

Reports表 #

+-----+---------+
| RId | Name    |
+-----+---------+
| 1   | Animals |
+-----+---------+
| 2   | Plants  |
+-----+---------+

ReportVersions表 #

每个报表对应一个或多个版本，版本关联必填的报表（FK_RId）

+-----------+---------------+--------+
| VersionId | VersionNumber | FK_RId |
+-----------+---------------+--------+
| 1         | 1             | 1      |
+-----------+---------------+--------+
| 2         | 2             | 1      |
+-----------+---------------+--------+
| 3         | 1             | 2      |
+-----------+---------------+--------+

ReportVersionHasAttributes表 #

ReportVersion与Attributes的多对多关联表

+-----------+-------------+
| VersionId | AttributeId |
+-----------+-------------+
| 1         | 1           |
+-----------+-------------+
| 2         | 2           |
+-----------+-------------+
| 3         | 3           |
+-----------+-------------+

Attributes表 #

+-------------+------+
| AttributeId | Name |
+-------------+------+
| 1           | A    |
+-------------+------+
| 2           | B    |
+-------------+------+
| 3           | C    |
+-------------+------+

期望输出结果 #

（报表1的版本1无需展示）

+-----+---------+-----------+---------------+--------+-----------+-------------+------+
| RId | Name    | VersionId | VersionNumber | FK_RId | VersionId | AttributeId | Name |
+-----+---------+-----------+---------------+--------+-----------+-------------+------+
| 1   | Animals | 2         | 2             | 1      | 2         | 2           | B    |
+-----+---------+-----------+---------------+--------+-----------+-------------+------+
| 2   | Plants  | 3         | 1             | 2      | 3         | 3           | C    |
+-----+---------+-----------+---------------+--------+-----------+-------------+------+

已实现的SQL #

SELECT
    Reports.RId,
    Reports.Name,
    ReportVersions.VersionId,
    ReportVersions.VersionNumber,
    ReportVersions.FK_RId,
    ReportVersionHasAttributes.VersionId,
    ReportVersionHasAttributes.AttributeId,
    Attributes.AttributeId,
    Attributes.Name

FROM [Reports]
INNER JOIN (
        SELECT Max([VersionId]) AS MaxVersionId
            ,[FK_RId]
        FROM [ReportVersions]
        GROUP BY [FK_RId]
    ) ON [FK_RId] = [RId]

INNER JOIN [ReportVersions] ON [ReportVersions].[VersionId] = [MaxVersionId]
INNER JOIN [ReportVersionHasAttributes] ON [ReportVersionHasAttributes].[VersionId] = [ReportVersions].[VersionId]
INNER JOIN [Attributes] ON [Attributes].[AttributeId] = [ReportVersionHasAttributes].[AttributeId]

EF Core实体类 #

public class Reports
{
    public int RId { get; set; }
    public string Name { get; set; }

    public List<ReportVersions> ReportVersions { get; set; }
}

public class ReportVersions
{
    public int VersionId { get; set; }
    public string Name { get; set; }
    public int VersionNumber { get; set; }
    public Reports FK_RId { get; set; }
    // 注：此处属性应为List<Attributes>，原代码笔误写为List<ReportVersions>
    public List<Attributes> Attributes { get; set; }
}

// ReportVersionHasAttributes未显式定义类，由EF Core管理，包含ReportVersions和Attributes的主键

public class Attributes
{
    public int AttributeId { get; set; }
    public string Name { get; set; }
    public List<ReportVersions> ReportVersions { get; set; }
}

我的LINQ尝试 #

var result = await _db.Reports
    .Include(x => x.ReportVersions)
    .ThenInclude(x => x.Attributes)
    .ToListAsync();

LINQ解决方案 #

1. 返回扁平列表（对应SQL的输出结构） #

可以通过子查询或分组先获取每个报表的最高版本ID，再关联查询完整数据：

方式一：基于分组的关联查询

// 先获取每个报表对应的最高版本ID
var maxVersionMap = await _db.ReportVersions
    .GroupBy(rv => rv.FK_RId.RId)
    .Select(g => new 
    {
        RId = g.Key,
        MaxVersionId = g.Max(rv => rv.VersionId)
    })
    .ToListAsync();

// 关联所有表获取最终扁平数据
var flatResult = await _db.Reports
    .Join(maxVersionMap, r => r.RId, m => m.RId, (r, m) => new { Report = r, m.MaxVersionId })
    .Join(_db.ReportVersions, rm => rm.MaxVersionId, rv => rv.VersionId, (rm, rv) => new { rm.Report, Version = rv })
    .Join(_db.ReportVersionHasAttributes, rrv => rrv.Version.VersionId, rva => rva.VersionId, (rrv, rva) => new { rrv.Report, rrv.Version, rva })
    .Join(_db.Attributes, rrva => rrva.rva.AttributeId, a => a.AttributeId, (rrva, a) => new 
    {
        rrva.Report.RId,
        rrva.Report.Name,
        rrva.Version.VersionId,
        rrva.Version.VersionNumber,
        FK_RId = rrva.Version.FK_RId.RId,
        rrva.rva.VersionId,
        rrva.rva.AttributeId,
        AttributeName = a.Name
    })
    .ToListAsync();

方式二：子查询筛选最高版本

var flatResult = await (from r in _db.Reports
                        join rv in _db.ReportVersions on r.RId equals rv.FK_RId.RId
                        // 筛选当前报表的最高版本
                        where rv.VersionId == (from rv2 in _db.ReportVersions
                                              where rv2.FK_RId.RId == r.RId
                                              select rv2.VersionId).Max()
                        join rva in _db.ReportVersionHasAttributes on rv.VersionId equals rva.VersionId
                        join a in _db.Attributes on rva.AttributeId equals a.AttributeId
                        select new 
                        {
                            r.RId,
                            r.Name,
                            rv.VersionId,
                            rv.VersionNumber,
                            FK_RId = rv.FK_RId.RId,
                            rva.VersionId,
                            rva.AttributeId,
                            AttributeName = a.Name
                        }).ToListAsync();

2. 返回嵌套结构（符合实体类模型） #

如果需要返回嵌套的对象结构（每个Reports仅包含最高版本的ReportVersions，每个ReportVersions包含对应Attributes），可以直接通过投影实现：

var nestedResult = await _db.Reports
    .Select(r => new Reports
    {
        RId = r.RId,
        Name = r.Name,
        ReportVersions = r.ReportVersions
            // 筛选当前报表的最高版本
            .Where(rv => rv.VersionId == r.ReportVersions.Max(rv2 => rv2.VersionId))
            .Select(rv => new ReportVersions
            {
                VersionId = rv.VersionId,
                VersionNumber = rv.VersionNumber,
                // 加载对应属性
                Attributes = rv.Attributes.ToList()
            })
            .ToList()
    })
    .ToListAsync();

或者用OrderByDescending+Take(1)简化筛选：

var nestedResult = await _db.Reports
    .Select(r => new Reports
    {
        RId = r.RId,
        Name = r.Name,
        ReportVersions = r.ReportVersions
            .OrderByDescending(rv => rv.VersionId)
            .Take(1)
            .Select(rv => new ReportVersions
            {
                VersionId = rv.VersionId,
                VersionNumber = rv.VersionNumber,
                Attributes = rv.Attributes.ToList()
            })
            .ToList()
    })
    .ToListAsync();

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