Linq group by with a sub query

Question

Hello,

I have an object that contains these three properties: ID, DATE and NAME and want to query it with linq yielding the same results as if written in the sql statement below.

SELECT ID, MAX(DATE), NAME 
FROM TABLE
GROUP BY ID

Here is what I have so far with linq:

var grouped = from a in table
group a by a.ID into g
select new fooObject()
{
DATE= g.Max(a => a.DATE),
NAME= g.Select(a => a.Name).ToString(),
ID= g.Select(a => a.ID)
};

Thanks in advance for any help.

Answer 1

var grouped = (from a in table where a.Date == (from d in table where a.ID == d.ID select Date).Max() select a).OrderBy(i => i.ID);

Answer 2

Given your updated explanation, here is my proposed solution. I've also cleaned up the code a bit.

var grouped = from item in table
              group item by item.Id into g
              let itemMaxDate = g.MaxBy(i => i.Date)
              select new FooObject()
              {
                  Id = g.Key,
                  Name = itemMaxDate.Name,
                  Date = itemMaxDate.Date
              };

You then need to define the MaxBy extension method. (Unfortunately LINQ does include one built-in.)

public static TSource MaxBy<TSource, TResult>(this IEnumerable<TSource> 
    collection, Func<TSource, TResult> func)
{
    var comparer = Comparer<TResult>.Default;
    TSource maxItem = null;
    foreach (var item in collection)
    {
        if (comparer.Compare(item, maxItem) > 0)
            maxItem = item;
    }
    return maxItem;
}

I believe this ought to be what you want now. Let me know if it does the job for you.

Answer 3

SELECT ID, MAX(DATE), NAME
FROM TABLE
GROUP BY ID

Column 'TABLE.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.