Linq - get top 2 records through criteria

Question

Hi folks,

i have data in following format:

colA                colB
2010                10
2010                20
2010                30
1999                99

I need to obtain output as follows using Linq/Lambda expression:

colA                colB
2010                10
1999                99

colB could be any non-99 value but the top 2 should always list 99 and any 1 instance of non-99 value. I am after the values in colA.

TIA

Answer 1

You'll want to learn how to group using GroupBy(); go to 101 LINQ Samples: Grouping Operators.

public class Record
{
    public int colA; { get; }
    public int colB; { get; }
}

...
// this extension method will clean up our searching within the group clause
public static Record Select99OrFirst(this IEnumerable<Record> source)
{
    var item = source.FirstOrDefault(r => r.colB == 99);
    return (null != item) ? item : source.First();
}

...

IEnumerable<Record> data;
var query = data.GroupBy(r => r.colA)
  .Select(g => new { Year = g.Key, Value = g.Select99OrFirst().colB })
  .OrderByDescending(x => x.Year);

Console.WriteLine("colA/tcolB");
foreach (var item in query)
    Console.WriteLine(item.Year + "/t" + item.Value);

This will give you anonymous objects with a Year property and a Value property for colA and colB respectively. Each will represent the first record within a year group if it couldn't find the 99 record in the group first.

Answer 2

Are you going for something like this?

var value1 = data.FirstOrDefault(x => x.colB != 99).Select(x => x.colA);
var value2 = data.FirstOrDefault(x => x.colB == 99).Select(x => x.colA);

Which could be combined into an IEnumerable using a utility method such as the following extension:

public static void CreateEnumerable<TResult>(this TResult obj)
{
    yield return obj;
}

In a manner like so:

var results = value1.CreateEnumerable().Concat(value2.CreateEnumerable());