Group and sort the datatable using Linq

Hi Everyone,

My Requirement is i need to group datatable based on a column (Manager Name) and after group i need to sort them based on 2columns in ascending order (service date) and (display name)

image
Input Data

image
Output Data

@ppr @Yoichi @Rahul_Unnikrishnan @Palaniyappan

Thanks in Advance

Hi,

How about the following?

dt.AsEnumerable.GroupBy(Function(r) r("Manager Name").ToString()).SelectMany(Function(g) g.OrderBy(Function(r) DateTime.Parse(r("Service Date").ToString)).ThenBy(Function(r) r("Display Name"))).CopyToDataTable

Sample20220624-4.zip (9.6 KB)

Regards,

@Yoichi thanku , but it throws error if service date column has empty values
String not recognized as valid datetime , any solution for that?

Hi,

If it’s empty, how should it be handled: the latest or the oldest?

Regards,

@Yoichi oldest , ascending order should be followed

HI,

Can you try the following expression?

dt.AsEnumerable.GroupBy(Function(r) r("Manager Name").ToString()).SelectMany(Function(g) g.OrderBy(Function(r) if(String.IsNullOrEmpty(r("Service Date").ToString),DateTime.MinValue,DateTime.Parse(r("Service Date").ToString))).ThenBy(Function(r) r("Display Name"))).CopyToDataTable

Regards,

@Yoichi thanks it works perfect, but only throws error if date column has wrong date format
eg: 13/09/201113

Is there any solution for this issue

Hi,

If there is invalid date string, how should it be handled: as the oldest or remove the row?

Regards,

@Yoichi always oldest (ascending) only , no need to remove the row

HI,

Give the following expression try.

dt.AsEnumerable.GroupBy(Function(r) r("Manager Name").ToString()).SelectMany(Function(g) g.OrderBy(Function(r) if(DateTime.TryParse(r("Service Date").ToString,New DateTime),DateTime.Parse(r("Service Date").ToString),DateTime.MinValue)).ThenBy(Function(r) r("Display Name"))).CopyToDataTable

Regards,

1 Like

@Yoichi its working correctly Thankyou sir!!

1 Like

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