LINQ to DataSet DataTable Group By - Syntax

Question

I have a Dataset that contains e.g. id, date, other fields...

I want to retrieve unique rows based on ids and latest date.

I have this query but I can't figure out where to put the date condition.

DataTable dt = ds.Tables[0]
                     .AsEnumerable()
                     .GroupBy(row => row.Field<string>("id"))
                     .Select(group => group.First())
                     .CopyToDataTable();

r.Text = dt.Rows.Count.ToString();

gv.DataSource = dt;
gv.DataBind();

The result should be

1 - 8/2/2014
2 - 1/8/2014
4 - 1/2/2011

Thanks,

Answer 1

You need to do OrderByDescending for Date Field and then select First:

 .Select(group => group.OrderByDescending(r=> r.Field<DateTime>("Date")).First())

So your query would be:

DataTable dt = ds.Tables[0].AsEnumerable()
                .GroupBy(row => row.Field<string>("id"))
                .Select(group => group
                                .OrderByDescending(r=> r.Field<DateTime>("Date"))
                                .First())
                .CopyToDataTable();

Answer 2

Maybe you can use this: LINQ to Datatable Group by and return all columns

And only return the field that you want, p.e. date