Select and grouping based on different fields/integer

Question

Table in SQL:

 Document            Revision             Version
 -----------------------------------------------------
   ABC                  1                    1
   ABC                  1                    2
   ABC                  1                    8
   ABC                  2                    3
   DocumentF            1                    3
   DocumentF            1                    2

Expected output:

 Document            Revision             Version
 -----------------------------------------------------
   ABC                     1                    8
   ABC                     2                    3
   DocumentF               1                    3

Basically if there are two versions of the same revision, bring back the latest based on version.

I've tried the following:

var list = from document in documents
           group document by document.Document
           into groups
           from g in groups
           group g by g.Revision
           into final
           select final.OrderByDescending(d => d.Version).FirstOrDefault();

The above code produces the following:

 Document            Revision             Version
 -----------------------------------------------------
   ABC                     2                    3
   DocumentF               1                    3

Salah Akbari · Answer 1 · 2020-02-10T06:57:15.363

1

You need to GroupBy multiple columns, something like this:

 var result = documents.GroupBy(c => new { c.Document, c.Revision })
        .Select(c => new DocumentClass
        {
            Document = c.Key.Document,
            Revision = c.Key.Revision,
            Version = c.Max(d=>d.Version)
        }).ToList();

edited Feb 10 '20 at 06:57

answered Feb 10 '20 at 05:04

Salah Akbari

39,330
10
79
109

Works great - is there a way to return the whole object instead of an anonymous type? Example. my list is List documents. Document class contains: Reference (which is ABC, DocumentF), Revision, Version, and a whole bunch of fields. – LuxC Feb 10 '20 at 05:58
@LuxC See my updated answer, all you need is just adding your class name to the `Select` part. – Salah Akbari Feb 10 '20 at 06:07
Sorry, what I meant was, is there a way to return the original object and not a new class? – LuxC Feb 11 '20 at 00:25
@LuxC You can and it's possible with a similar way, but it has not been recommended, you should always return a DTO class, See this post for more details https://stackoverflow.com/questions/21554977/should-services-always-return-dtos-or-can-they-also-return-domain-models – Salah Akbari Feb 11 '20 at 02:43

score 1 · Accepted Answer · answered Feb 10 '20 at 05:38

Using Linq , we can group by multiple properties like below

 var list = from document in documents
                       group document by (document.Document, document.Revision)
                      into groups
                       select groups.OrderByDescending(d => d.Version).FirstOrDefault();

Select and grouping based on different fields/integer

2 Answers2