Linq to dataset select row based on max value of column

Question

I have a dataset table, I want to group it by column MOID, and then within this group I want to select the row which has max value of column radi.

Can anybody show me how to do it via LINQ to dataset?

Answer 1

Although the solution posted by Barry should work (with a few fixes), it is sub-optimal : you don't need to sort a collection to find the item with the maximum value of a field. I wrote a WithMax extension method, which returns the item with the maximum value of the specified function :

    public static T WithMax<T, TValue>(this IEnumerable<T> source, Func<T, TValue> selector)
    {
        var max = default(TValue);
        var withMax = default(T);
        bool first = true;
        var comparer = Comparer<TValue>.Default;
        foreach (var item in source)
        {
            var value = selector(item);
            int compare = comparer.Compare(value, max);

            if (compare > 0 || first)
            {
                max = value;
                withMax = item;
            }
            first = false;
        }
        return withMax;
    }

It iterates the collection only once, which is much faster than sorting it just to get the first item.

You can then use it as follows

var query =
    from row in table.AsEnumerable()
    group row by row.Field<int>("MOID") into g
    select g.WithMax(r => r.Field<int>("radi"));

Answer 2

This is untested but I think something like this should work:

        var qry = from m in [YourDataSource]
                      group p by m.MOID into grp
                      select grp.OrderByDescending(a => a.RADI).First();

Answer 3

this works with one query!

public static T WithMax<T, TValue>(this IEnumerable<T> source, Func<T, TValue> keySelector)
{
    return source.OrderByDescending(keySelector).FirstOrDefault();
}