LINQ选择行"max"版本 [英] LINQ Select rows mith "max" version

问题描述

我正在使用LINQ，试图选择每个"CaseId"中"version"列最大的行.

I'm using LINQ and I trying to select rows in which the "version" column is max for EVERY "CaseId".

这是带有数据的示例表:

This is an example table with data:

╔═════════╦══════════╦═════════╦══════════╗
║ Id      ║ CaseId   ║ Version ║ ParentId ║
╠═════════╬══════════╬═════════╬══════════╣
║ 1       ║ A        ║    0    ║          ║
║ 2       ║ A        ║    1    ║    1     ║
║ 3       ║ A        ║    2    ║    2     ║
║ 4       ║ B        ║    0    ║          ║
║ 5       ║ B        ║    1    ║    4     ║
║ 6       ║ C        ║    0    ║          ║
╚═════════╩══════════╩═════════╩══════════╝

所需的结果将是:

╔═════════╦══════════╦═════════╦══════════╗
║ Id      ║ CaseId   ║ Version ║ ParentId ║
╠═════════╬══════════╬═════════╬══════════╣
║ 3       ║ A        ║    2    ║    2     ║
║ 5       ║ B        ║    1    ║    4     ║
║ 6       ║ C        ║    0    ║          ║
╚═════════╩══════════╩═════════╩══════════╝

我正在使用的LINQ如下:

The LINQ I'm using is the following:

IEnumerable<Case> list =
    (from c in db.DBCases
     let maxVersion = db.DBCases.Max(c => c.Version)
     where (c.Version == maxVersion)
     orderby c.CaseId descending
     select c);

这目前仅返回具有WHOLE表最高版本的行，但省略了所有其他记录.

This is currently returning only the row with the max version of the WHOLE table, but is omitting all the other records.

╔═════════╦══════════╦═════════╦══════════╗
║ Id      ║ CaseId   ║ Version ║ ParentId ║
╠═════════╬══════════╬═════════╬══════════╣
║ 3       ║ A        ║    2    ║    2     ║
╚═════════╩══════════╩═════════╩══════════╝

推荐答案

您的where子句告诉您准确地说出的内容(只有Cases的Version等于整个表的最大版本) .这样想吧:

Your where clause is telling it to get exactly what you said (only Cases that have a Version equal to the max version of the whole table). Think of it this way:

// This is 2.
int maxVersionOfWholeTable = db.DBCases.Max(c => c.Version);

// This is essentially what your query is doing:
var query = from c in db.DBCases
            where c.Version == maxVersionOfWholeTable
            select c;

相反，您可以使用分组来获得所需的结果:

Instead, you can use a grouping to achieve the desired result:

var query = from c in db.DBCases
            group c by c.CaseId into g
            orderby g.Key descending
            select g.OrderByDescending(x => x.Version).FirstOrDefault()

此版本说:

首先，按CaseId将Cases分组，为您提供类似的信息:

First, put Cases into groups by CaseId, giving you something like this:

Group 1 (CaseId = A): 1, 2, 3
Group 2 (CaseId = B): 4, 5
Group 3 (CaseId = C): 6

然后针对每个组，按版本排序并获得最高记录，如下所示:

Then for each of those groups, order by the Version and get the top record like so:

Group 1 Ordered: [3], 2, 1
Group 2 Ordered: [5], 4
Group 3 Ordered: [6]

结果:3、5、6.

编辑-回到这个问题，意识到g.OrderByDescending(x => x.Version).FirstOrDefault()行对于解释正在发生的事情非常有用，但是在这种情况下使用Max()会更清晰一些，例如:

Edit - Came back to this question and realized the g.OrderByDescending(x => x.Version).FirstOrDefault() line was good for explaining what was going on, but it's a bit clearer to use Max() in this case, like so:

var query = from c in db.DBCases
            group c by c.CaseId into g
            orderby g.Key descending
            select g.Max(x => x.Version)

这篇关于LINQ选择行"max"版本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！