VB LINQ groupby orderby SelectMany

Help
1

from datatable I want to, group by EmpID and get all the record with latest date (as multiple record can exist for same date)
I managed to get till here

dtPayInfo.AsEnumerable.GroupBy(Function(r ) r(“PaymentInformationV3_worker”)).Select(Function (g) g.OrderByDescending(Function(r ) Convert.ToDateTime(r(“PaymentInformationV3_effectiveStartDate”).ToString)).First ).CopyToDataTable()

but it is selecting only First row…
so I want to know how can I modify the above LINQ query to change it from Select to SelectMany so that it can select multiple records with latest date

below is sample datatable

effectiveStartDate workerID
2015-05-01T00:00:00Z 123
2015-05-01T00:00:00Z 1234
2016-06-01T00:00:00Z 12345
2016-06-01T00:00:00Z 12346
2017-10-01T00:00:00Z 535
2016-06-01T00:00:00Z 535
2016-06-01T00:00:00Z 5145
2018-04-01T00:00:00Z 16846
2018-04-01T00:00:00Z 16846
2016-08-01T00:00:00Z 16846
2016-08-01T00:00:00Z 16846
2016-02-24T00:00:00Z 16846
2016-02-24T00:00:00Z 16846
1980-01-02T00:00:00Z 25154

I want to do it with LINQ only.

1 Like
2

You have to remove First from your query.

dtPayInfo.AsEnumerable.GroupBy(Function(r ) r(“PaymentInformationV3_worker”)).Select(Function (g) g.OrderByDescending(Function(r ) Convert.ToDateTime(r(“PaymentInformationV3_effectiveStartDate”).ToString))).CopyToDataTable()

3

but I want to select all the row with latest date only…
for example for workerID 16846
I want

2018-04-01T00:00:00Z 16846
2018-04-01T00:00:00Z 16846

i.e. only two records

4

Managed to solve it from this link

dtPayInfo.AsEnumerable.GroupBy(Function(r) r("workerID")).SelectMany(Function(g) g.Where(Function(r) Convert.ToDateTime(r("effectiveStartDate").ToString) = g.max(Function(c) Convert.ToDateTime(c("effectiveStartDate")) ))).CopyToDataTable()
3 Likes
5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.