Query with Linq and EF Core to obtain only the highest version in each case

Question

I get the following query without problems in SQL, but not in Linq. A list of reports with the last (highest) version and the corresponding attributes is to be output. Each report has one or more versions. However, I am only interested in the last/highest version of each report.

Reports:

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

ReportVersions: (each report has one or more versions) m:n Each version has a mandatory report (FK_RId).

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

ReportVersionHasAttributes: m:n Relationship table between ReportVersion and ReportAttributes

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

Each report version has one or more attributes. (m:n) Attributes:

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

This data should be output: In this case, version 1 of report 1 is of no interest.

+-----+---------+-----------+---------------+--------+-----------+-------------+------+
| 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    |
+-----+---------+-----------+---------------+--------+-----------+-------------+------+

My approach:

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

I assume I have to use the Max function, but I don't know where and how. I would appreciate any help.

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]

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; }
    public List<ReportVersions> Attributes { get; set; }
}

// ReportVersionHasAttributes is not explicitly defined as a class but is managed by EF Core. It contains the primary keys from the ReportVersions and attributes.



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

Additional question: Is it possible to get not only a flat list as a result but nested lists according to the class model? Example: Reports would have a List and in each highest ReportVersion the corresponding attributes are found in a List.

Answer 1

If I correctly understand your classes structure, your query should look like this one:

var query = 
    from r in _db.Reports
    from v in r.ReportVersions
        .OrderByDescending(v => v.ReportVersions)
        .Take(1)
    from a in v.Attributes
    select new 
    {
        r.Rid,
        r.Name,
        v.VersionId,
        v.VersionNumber,
        v.FK_RId,
        a.AttributeId,
        AttributeName = a.Name
    };