Group and sort the datatable using Linq

[kavya.s16]

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)

@ppr @Yoichi @Rahul_Unnikrishnan @Palaniyappan

Thanks in Advance

[Yoichi]

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,

[kavya.s16]

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

[Yoichi]

Hi,

@kavya.s16

if service date column has empty values

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

Regards,

[kavya.s16]

@Yoichi oldest , ascending order should be followed

[Yoichi]

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,

[kavya.s16]

@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

[Yoichi]

Hi,

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

Regards,

[kavya.s16]

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

[Yoichi]

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,

[kavya.s16]

@Yoichi its working correctly Thankyou sir!!