LINQ Query to Group by Multiple and Order

Question

I have the following data structure:

public class Difference
{
    public Difference() { }
    public Differential Type { get; set; }
    public int Magnitude { get; set; }
}
public enum Differential
{
    low,
    middle,
    high
}

I may have the following data:

Magnitude      Type
4456           low
4456           low
4423           low
4421           low
1000           high
1000           high
1001           high
1560           middle
4456           low
4421           low

Im trying to return a dataset that gives me the Count on the number identical maginitudes and their types BUT ONLY THE TOP IN EACH TYPE in descending order of count so for the above (in order)

Value: 4456 Type: Low Count: 3

Value: 1000 Type: High Count: 2

Value: 1560 Type: Middle Count: 1

Notice how 4421 at a count of 2 wasn't in there because 4456 had a larger count?

i think i have got close but not quite there:

var query = (from value in differences
             group value by new {value.Magnitude, value.Type} into groupjoin
             select new
             {
                 Value = groupjoin.Key.Magnitude,
                 Type = groupjoin.Key.Type,
                 Count = groupjoin.Count()                                         
             })
            .OrderByDescending(v => v.Count)
            .ThenByDescending(v => v.Value).ThenByDescending(v => v.Type).ToList();

UPDATE

Based on what was suggested below by Douglas ive managed to implement it with the following. Im not too concerned though if there are ties, i would like to return just the First() one (for each type) if possible. i can only do this by looping:

var query = (from value in differences
                                 /*where value.type == Differential.low*/
                                 group value by new {value.Magnitude, value.Type} into groupjoin
                                 select new
                                 {
                                     Value = groupjoin.Key.Magnitude,
                                     Type = groupjoin.Key.Type,
                                     Count = groupjoin.Count()                                         
                                 });

                    var query2 = query.Where(g1 => !query.Any(g2 =>
                                    g2.Type == g1.Type &&
                                    g2.Count > g1.Count));

                    int highest = 0;
                    int lowest = 0;
                    int middle = 0;
                    foreach (var item in query2)
                    {
                        if (item.Type == Differential.high && item.Count > highest) 
                        {
                            oresult.numberOfHighHits = item.Count;
                            oresult.highTimeMagnitude = item.Value;
                        }
                        if (item.Type == Differential.low && item.Count > lowest)
                        {
                            oresult.numberOfLowHits = item.Count;
                            oresult.lowTimeMagnitude = item.Value;
                        }
                        if (item.Type == Differential.middle && item.Count > middle)
                        {
                            oresult.numberOfMiddleHits = item.Count;
                            oresult.middleTimeMagnitude = item.Value;
                        }
                    }

And It would be good to have a code to construct your sample data. I am too lazy to copy/paste your data to my List<Difference> list= — L.B
– L.B, Commented Feb 12, 2012 at 21:23

Douglas · Accepted Answer · 2012-02-13 18:49:03Z

I’m preserving the first part of your LINQ query that gives the groupings of distinct magnitude–type pairs (and their counts):

var query = 
    from value in differences                                     
    group value by new {value.Magnitude, value.Type} into groupjoin
    select new
    {
        Value = groupjoin.Key.Magnitude,
        Type = groupjoin.Key.Type,
        Count = groupjoin.Count()                                         
    };

Then, I’m writing another LINQ which takes all the groupings produced by the first query and, for each grouping g1, checks that there does not exist any other grouping g2 which has the same type and a larger count. This would ensure that only the groupings with the largest count of their type are returned. In the case of ties (multiple groupings of the same type have the same largest count), all top groupings are returned.

var query2 = query.Where(g1 => 
    !query.Any(g2 => 
        g2.Type == g1.Type && 
        g2.Count > g1.Count));

Edit: To return a single result for each type even in the case of ties, we could add another condition to our query2 filter such that, if two groupings have the same Type and the same Count, then the one with the larger Value (originally named Magnitude) is picked. If you would prefer to pick the one with the smaller Value, just replace the last > with a <.

This approach would make the results of your query deterministic (rather than arbitrarily dependent on, for example, which grouping occurs first in the original list).

var query2 = query.Where(g1 => 
    !query.Any(g2 => 
        g2.Type == g1.Type && 
        (g2.Count > g1.Count || (g2.Count == g1.Count && g2.Value > g1.Value))));

thanks douglas, that worked well! i have put an update in my initial posting. Are you able to help?

Collectives™ on Stack Overflow

LINQ Query to Group by Multiple and Order

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related