CODEWITHSUNDEEP
linq
cvsr1980
cvsr1980

Reputation: 101

How to get the Max() of a Count() with LINQ

I'm new to LINQ and I have this situation. I have this table:

ID Date  Range
1 10/10/10 9-10
2 10/10/10 9-10
3 10/10/10 9-10
4 10/10/10 8-9
5 10/11/10 1-2
6 10/11/10 1-2
7 10/12/10 5-6

I just want to list the Maximun of rows per date by range, like this:

Date  Range  Total
10/10/10 9-10  3
10/11/10 1-2  2
10/12/10 5-6  1

I want to do this by using LINQ, do you have any ideas of how to do this?

Upvotes: 10

Views: 12206

Answers (4)

StriplingWarrior
StriplingWarrior

Reputation: 156459

I think something along these lines should work:

List<MyTable> items = GetItems();
var orderedByMax = from i in items
                   group i by i.Date into g
                   let q = g.GroupBy(i => i.Range)
                            .Select(g2 => new {Range = g2.Key, Count = g2.Count()})
                            .OrderByDescending(i => i.Count)
                   let max = q.FirstOrDefault()
                   select new {
                      Date = g.Key,
                      Range = max.Range,
                      Total = max.Count
                   };

Upvotes: 6

Ecyrb
Ecyrb

Reputation: 2080

This approach:
1) Groups by Date
2) For each Date, groups by Range and calculates the Total
3) For each Date, selects the item with the greatest Total
4) You end up with your result

public sealed class Program
{
    public static void Main(string[] args)
    {
        var items = new[]
        {
            new { ID = 1, Date = new DateTime(10, 10, 10), Range = "9-10" },
            new { ID = 2, Date = new DateTime(10, 10, 10), Range = "9-10" },
            new { ID = 3, Date = new DateTime(10, 10, 10), Range = "9-10" },
            new { ID = 4, Date = new DateTime(10, 10, 10), Range = "8-9" },
            new { ID = 5, Date = new DateTime(10, 10, 11), Range = "1-2" },
            new { ID = 6, Date = new DateTime(10, 10, 11), Range = "1-2" },
            new { ID = 7, Date = new DateTime(10, 10, 12), Range = "5-6" },
        };

        var itemsWithTotals = items
            .GroupBy(item => item.Date)  // Group by Date.
            .Select(groupByDate => groupByDate
                .GroupBy(item => item.Range)  // Group by Range.
                .Select(groupByRange => new
                {
                    Date = groupByDate.Key,
                    Range = groupByRange.Key,
                    Total = groupByRange.Count()
                })  // Got the totals for each grouping.
                .MaxElement(item => item.Total));  // For each Date, grab the item (grouped by Range) with the greatest Total.

        foreach (var item in itemsWithTotals)
            Console.WriteLine("{0} {1} {2}", item.Date.ToShortDateString(), item.Range, item.Total);

        Console.Read();
    }
}

/// <summary>
/// From the book LINQ in Action, Listing 5.35.
/// </summary>
static class ExtensionMethods
{
    public static TElement MaxElement<TElement, TData>(this IEnumerable<TElement> source, Func<TElement, TData> selector) where TData : IComparable<TData>
    {
        if (source == null)
            throw new ArgumentNullException("source");
        if (selector == null)
            throw new ArgumentNullException("selector");

        bool firstElement = true;
        TElement result = default(TElement);
        TData maxValue = default(TData);
        foreach (TElement element in source)
        {
            var candidate = selector(element);
            if (firstElement || (candidate.CompareTo(maxValue) > 0))
            {
                firstElement = false;
                maxValue = candidate;
                result = element;
            }
        }
        return result;
    }
}

According to LINQ in Action (Chapter 5.3.3 - Will LINQ to Objects hurt the performance of my code?), using the MaxElement extension method is one of the most effecient approaches. I think the performance would be O(4n); one for the first GroupBy, two for the second GroupBy, three for the Count(), and four for loop within MaxElement.

DrDro's approach is going to be more like O(n^2) since it loops the entire list for each item in the list.

StriplingWarrior's approach is going to be closer to O(n log n) because it sorts the items. Though I'll admit, there may be some crazy magic in there that I don't understand.

Upvotes: 0

Jla
Jla

Reputation: 11374

Using extension methods:

List<MyTable> items = GetItems();

var rangeTotals = items.GroupBy(x => new { x.Date, x.Range }) // Group by Date + Range
                  .Select(g => new { 
                              Date = g.Key.Date, 
                              Range = g.Key.Range, 
                              Total = g.Count() // Count total of identical ranges per date
                              });

var rangeMaxTotals = rangeTotals.Where(rt => !rangeTotals.Any(z => z.Date == rt.Date && z.Total > rt.Total)); // Get maximum totals for each date

Upvotes: 5

Abe Miessler
Abe Miessler

Reputation: 85036

unfortunately I can't test this at the moment but give this a try:

List<MyTable> items = GetItems();
items.Max(t=>t.Range.Distinct().Count());

Upvotes: 1

Related Questions