CODEWITHSUNDEEP
c#linqdatasetlinq-to-dataset
Benk
Benk

Reputation: 1312

LINQ to DataSet DataTable Group By - Syntax

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

enter image description here

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,

Upvotes: 0

Views: 3109

Answers (2)

Habib
Habib

Reputation: 223247

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

Upvotes: 2

user1998568
user1998568

Reputation: 1

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

Upvotes: 0

Related Questions