Select max as second criteria with Linq

Question

i'm trying to get all rows who has some field = max of that field

to be able to explain my doubt, I will leave an example of model

class model
{
    [Key]
    [Column(Order=0)]
    public int Key { get; set; }     

    [Key]
    [Column(Order=1)]
    public int Revision { get; set; }

    public string OtherStuff { get; set; }    
}

A data example could be

|Key|Revision   |Otherstuff |
|1  |1          |A          |
|1  |2          |B          |
|1  |3          |C          |
|2  |1          |D          |
|2  |2          |E          |
|3  |1          |F          |

What i want to get:

|Key|Revision   |Otherstuff |
|1  |3          |C          |
|2  |2          |E          |
|3  |1          |F          |

I try the solutions i could find here but i couldn't make it work

This it what i got

IQueryable<model> data = dbContext.model;

data = data.GroupBy(x => x.Revision ).OrderByDescending(x => x.Key).SelectMany(x => x)

//Applying logic to filter the data 
data = data.Where(...)

My main problem is that it is a filtering method, and when using SelectMany the database is disposed and I can not continue modifying the list as I need

How i supose to do it?

Thanks!

Solved

data = data.GroupBy(x => x.Key).Select(x => x.OrderByDescending(d => d.Revision).FirstOrDefault());

Thanks all responses!

Remove the SelectMany. The GroupBy returns a two dimensional array data[key, values[]]. The Where or Select then enumerates through the keys. You can then have a second Select like following : data.Select(x => x.Where(y => .....)).SelectMany(x => x).ToList(); — jdweng, Aug 23 '18 at 14:10

score 2 · Answer 1 · answered Aug 23 '18 at 14:13

2

Try this

data = data..GroupBy(x => x.Key).OrderByDescending(x => x.Key).Select(x => new { Key = x.Key, Revision = x.Count() });

answered Aug 23 '18 at 14:13

Arun Kumar

865
4
11

score 1 · Accepted Answer · answered Aug 23 '18 at 14:30

1

var result = data.GroupBy(x => x.Revision).Select(x => x.First())

if you try to do any other operations after the first query (such as select a subset of columns) - you need to use x.FirstOrDefault

answered Aug 23 '18 at 14:30

Jordi Jordi

451
2
9

with a bit of mutation it managed to solve my problem, thanks so much! – Juan Salvador Portugal Aug 23 '18 at 14:41

score 0 · Answer 3 · answered Aug 23 '18 at 14:09

0

data = data.GroupBy(x => x.Revision).Select(revisitions => revisitions.OrderByDescending(x => x.Key).First())

answered Aug 23 '18 at 14:09

Fedor

161
5

Select max as second criteria with Linq

3 Answers3