CODEWITHSUNDEEP
asp.net-mvcentity-frameworklinq
Citizen SP
Citizen SP

Reputation: 1411

Linq sort by 2 columns with FirstOrDefault

I try to sort my Linq query on 2 columns: seq and version:

table:

id | seq | version
01 | 1   | 1
02 | 1   | 2
03 | 1   | 1
04 | 2   | 2
05 | 2   | 3
06 | 3   | 1

My query:

  var events = from x in db.Events
                         group x by x.Seq into s
                         select s.OrderBy(z => z.Seq).OrderByDescending(z => z.version).FirstOrDefault();

Current result:

id | seq | version
05 | 2   | 3
06 | 3   | 1
02 | 1   | 2

Desired result:

id | seq | version
02 | 1   | 2
05 | 2   | 3
06 | 3   | 1

The problem seems to be the combination or 2 orderby and FirstOrDefault(). What is the correct query to solve this?

Upvotes: 1

Views: 462

Answers (2)

Alex Art.
Alex Art.

Reputation: 8781

FIXED 

var events = (from x in db.Events
           group x by x.Seq into s
           select s.OrderByDescending(z => z.version).FirstOrDefault()).OrderBy(z => z.Seq);

See working Fiddle

Upvotes: 2

Ivan Stoev
Ivan Stoev

Reputation: 205889

The correct way is to first group by seq, then order by version and take the desired single result, and finally sort the resulting set by seq, like this

var events = data
    .GroupBy(x => x.seq, (key, items) => items.OrderByDescending(x => x.version).FirstOrDefault())
    .OrderBy(x => x.seq);

Upvotes: 1

Related Questions