Linq to dataset select row based on max value of column

Question

Hello, I have a dataset table, i want to group it by column "MOID", and then with in this group want to select the row which has max value of coulmn "radi"

can anybody show me how to do it via LINQ to dataset

Answer 1

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 2

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"));