Linq Group By With Having

Question

Here is my example:

class test{
    public DateTime dt;
    public double value;
    public int id;
}

i have:

IEnumerable<Test> TestList;

I want select rows from it, with group by id, with max(dt).

my query:

var q = from p in TestList
        group p by p.id
        into g
        select new { id = g.Key, dt = g.Max(w => w.dt) }); 

In result i have anonyoumus class with {id_param,dt}, but i want to have field "value" too, like {id,dt,value},how can i do it?

Answer 1

You have to group by all fields that are not aggregated. So value needs to be summed up or grouped by.

Try:

        var result = TestList
            .GroupBy(t => t.id)
            .Select(g => new { id = g.Key, g.OrderByDescending(c => c.dt).First().dt, g.OrderByDescending(c => c.dt).First().value });

Answer 2

Based on comments and the question, you want: for each distinct id, the instance with the maximum dt.

I would add a help method: MaxBy which allows a whole object to be selected based on the value of a function¹:

public static T MaxBy<T,TValue>(this IEnumerable<T> input, Func<T,TValue> projector)
                                where TValue : IComparable<TValue> {
  T found = default(T);
  TValue max = default(TValue);
  foreach (T t in input) {
    TValue p = projector(t);
    if (max.CompareTo(p) > 0) {
      found = t;
      max = p;
    }
  }
  return found;
}

And then the query becomes:

var q = from p in TestList
        group p by p.id into g
        select g.MaxBy(w => w.dt);

NB. this implementation of MaxBy will only work for objects where the value of the member being compared is greater than its type's default value (e.g. for int: greater than zero). A better implementation of MaxBy would use the enumerator manually and initialise both found and max variables directly from the first element of the input.

---

¹ if you are using The Reactive Extensions (Rx) this is included in the System.Interactive assembly.