CODEWITHSUNDEEP
c#linq
user1112324
user1112324

Reputation: 643

LINQ Query to Group by Multiple and Order

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;
                        }
                    }

Upvotes: 1

Views: 4872

Answers (1)

Douglas
Douglas

Reputation: 54877

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))));

Upvotes: 3

Related Questions