Linq distinct and max: How to get rid of duplicates

Question

I have the following list:

var items = new List<Tuple<string, int, int>>()
            {
                 Tuple.Create("A", 3, 0),
                 Tuple.Create("A", 5, 0),
                 Tuple.Create("B", 1, 0),
                 Tuple.Create("C", 1, 0),
                 Tuple.Create("C", 3, 0),
                 Tuple.Create("C", 2, 0),
                 Tuple.Create("C", 3, 1)
            };

I have the following linq:

var results = (from item in items
                           group item by item.Item1 into groupedItems
                           let maxPriority = groupedItems.Max(item => item.Item2)
                           from element in groupedItems
                           where element.Item2 == maxPriority
                           select element).Distinct();

I am getting this:

Name    Priority
A       5
B       1
C       3
C       3

I would like only distinct as follows:

Name    Priority
A       5
B       1
C       3

Does anyone know how to modify the linq to do that? Thank you before hand.

Note: I am aware that Distinct has overloads that allow you to pass an IComparer. But I would like to avoid this if it can be done simpler on the linq statement itself. I am not stuck on using the Distinct statement neither.

Note that `Distinct` has overloads that allow you to pass an IEqualityComparer (https://learn.microsoft.com/en-us/dotnet/api/system.linq.queryable.distinct?view=netframework-4.7.1#System_Linq_Queryable_Distinct__1_System_Linq_IQueryable___0__System_Collections_Generic_IEqualityComparer___0__). Write a custom equality comparer based on IEqualityComparer that compares your tuples in a way so that `Distinct` will be able to recognize equal tuples based on Item1 and Item2 (or whatever else criteria)... — , Mar 01 '19 at 21:19

score 2 · Answer 1 · answered Mar 01 '19 at 21:59

Fundamentally the operation you're trying to do is to get the item from a sequence who has the max value for a particular selector function. So just write that function (or use one written by someone else) to do that. Note that in your query you're iterating the sequence multiple times in order to accomplish that, and you can do better. You only need a single pass to implement that operation.

Now all you're doing is performing a single operation on the grouped items, and your query becomes very simple:

var query = items.GroupBy(item => item.Item1)
    .Select(group => group.MaxBy(item => item.Item2));

Christoph Herold · Accepted Answer · 2019-03-01T21:46:03.623

Well, your query contains all properties of your items, so when I execute this, the result is actually

Item1  Item2  Item3
A      5      0
B      1      0
C      3      0
C      3      1

So, your Distinct call is actually behaving correctly. I assume, since you are only looking at the first two properties, you should also only include those in the result. Then, the distinct call actually gives you the result you want:

var results = (from item in items
               group item by item.Item1 into groupedItems
               let maxPriority = groupedItems.Max(item => item.Item2)
               from element in groupedItems
               where element.Item2 == maxPriority
               select new {element.Item1, element.Item2}).Distinct();

Note the replaced select new { ... }.

UPDATE:

Then, your query would be similar to what Matt is suggesting:

var results = (from item in items
               group item by item.Item1 into groupedItems
               let maxPriority = groupedItems.Max(item => item.Item2)
               from element in groupedItems
               where element.Item2 == maxPriority
               group element by new { element.Item1, element.Item2 } into
               groupByItem1Item2
               select groupByItem1Item2.OrderByDescending(element => element.Item3).First());

Simply do the .OrderByDescending(element => element.Item3) before the .First() call.

Thank you for your input Christoph. But unfortunately that would not work since I still need to read the data in the third column later on for other purposes. — Robert Smith, Mar 01 '19 at 21:29
Ok, but then your data becomes ambiguous. Which of the two entries for "C" should be used? The one with Item3 = 0 or Item3 = 1? If you don't resolve this, you will not be able to get a correct "Distinct". — Christoph Herold, Mar 01 '19 at 21:32
Christoph, sorry I left out that detail! The one that gets selected should be the one with the highest Item3 value. — Robert Smith, Mar 01 '19 at 21:40
See my update. Matt's basics are perfect, I just added the missing rule. — Christoph Herold, Mar 01 '19 at 21:46

score 0 · Answer 3 · answered Mar 01 '19 at 21:37

Add another group by on Item1, Item2 like:

var results = (from item in items
                   group item by item.Item1 into groupedItems
                   let maxPriority = groupedItems.Max(item => item.Item2)
                   from element in groupedItems
                   where element.Item2 == maxPriority
                   group element by new { element.Item1, element.Item2 } into 
                   groupByItem1Item2
                   select groupByItem1Item2.First());

score 0 · Answer 4 · answered Mar 01 '19 at 22:14

Here is a pure LINQ solution using lambas and without adding any third party libraries:

var results = items.GroupBy(item => item.Item1)
    .Select(grp => new {
        Group = grp,
        Item2Max = grp.Max(item => item.Item2)
    })
    .Select(x => new Tuple<string, int, int>(
        x.Group.Key,
        x.Item2Max,
        x.Group.Where(item => item.Item2 == x.Item2Max).Max(item => item.Item3)
    ));

This outputs:

Item1   Item2   Item3
A       5       0
B       1       0
C       3       1

Linq distinct and max: How to get rid of duplicates

4 Answers4