CODEWITHSUNDEEP
c#linq
Andy McCarthy
Andy McCarthy

Reputation: 11

LINQ query to select top 3 from a group and get the average of those selected

I would like to:

  1. Select for each schedule_Region_ids the bins that do not fail qc (QC=1)
  2. Select the top 3 bins that do not have QC fail mark (1) (for example, bins 3 4 and 5 if no QC mark =1)
  3. Average the 3 selected values to create 'BaselPeak' for each schedule_Region_id.

I have tried the following code:

var categories = drugonboard
    .Where(p => p.Field<int?>("QC") != 1)
    .OrderByDescending(x => x.Field<int>("bin")).Take(3)
    .GroupBy(t => new { ID = t.Field<int>("schedule_region_id")})
    .Select(g => new
    {
      schedule_region_id = g.Key.ID,
      BaselPeak = g.Average(p => p.Field<double>("AnalytePeak"))
    });

However I end up with the top 3 schedule_region_ids and not the average of the top three bins for each schedule_region_id. If I try to GroupBy first then I get anonymous type errors.

I have a dataset which looks like this:

id | bin | AnalytePeak | QC
 1 |  1  |         620 | 
 1 |  2  |        1020 |
 1 |  3  |         681 |  1
 1 |  4  |        1190 |   
 1 |  5  |        1200 |    
 ---------------------------
 2 |  1  |        1020 |
 2 |  2  |        1076 |
 2 |  3  |        1190 |
 2 |  4  |        1200 |
 2 |  5  |         358 |  1
 ---------------------------
 3 |  1  |        1020 |
 3 |  2  |        1076 |
 3 |  3  |        1190 |
 3 |  4  |        1200 |
 3 |  5  |        1358 |

Upvotes: 1

Views: 296

Answers (1)

Robert McKee
Robert McKee

Reputation: 21477

You need to take 3 after grouping and after sorting the child records.

var categories = drugonboard
  .Where(p => p.Field<int?>("QC") != 1)
  .GroupBy(t => new { ID = t.Field<int>("schedule_region_id")})
  .Select(g => new {
    schedule_region_id = g.Key.ID,
    BaselPeak = g.OrderByDescending(x => x.Field<int>("bin"))
      .Take(3)
      .Average(p => p.Field<double>("AnalytePeak"))
  });

This assumes: If all bins are QC=1, then you don't want the schedule_region_id parent at all and by top 3, you mean the highest 3 bin numbers.

Upvotes: 1

Related Questions