CODEWITHSUNDEEP
linqlinq-to-sqlmax
Javier
Javier

Reputation: 2159

LINQ Select rows mith "max" 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    ║          ║
╚═════════╩══════════╩═════════╩══════════╝

The desired result would be:

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

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);

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     ║
╚═════════╩══════════╩═════════╩══════════╝

Upvotes: 2

Views: 2135

Answers (2)

Ocelot20
Ocelot20

Reputation: 10800

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()

This version says:

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]

Resulting in: 3, 5, 6.

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)

Upvotes: 7

PCG
PCG

Reputation: 1207

What about doing something like the following. It will return a dictionary withe caseid as the key and the max version as the value.

       list.GroupBy(x => x.CaseId).ToDictionary(x => x.Key, x => x.Max(c => c.Version));

Upvotes: 0

Related Questions