vb.netlinquipath

GroupBy Datatable with multiple column values


I have to group by PatientMedicareNumber, startdateofservice and enddateofservice.

Input is like this: Input

I have groupby only using patientmedicarenumber which is working fine. However, if I want to include startdate and enddate the logic is not working as expected and showing null error.

in_dt_WPSFileInput.AsEnumerable().GroupBy(Function(r) New With{Key.Supplier = r("PatientMedicareNumber")}).[Select](Function(g) g.OrderBy(Function(r) r("PatientMedicareNumber")).First()).CopyToDataTable()

I want to have 7 groupby which have to generate result as 7 datatable as follows output

Found some queries online and using this but this result same as the input -

(From d In in_dt_WPSFileInput
Group d By a1=d("PatientMedicareNumber").tostring.trim,a2=d("DateofServiceStart").tostring.trim,a3=d("DateofServiceEnd").tostring.trim Into grp=Group
Where grp.count>=1
Select grp.tolist).SelectMany(Function(x) x).copytodatatable()

How to groupby using multiple columns with same value match?


Solution

  • As you mentioned that you want 7 datatables instead of one then you can use the below LINQ:

    (From d In in_dt_WPSFileInput
    Group d By a1=d("PatientMedicareNumber").tostring,a2=d("DateofServiceStart").tostring,a3=d("DateofServiceEnd").tostring Into grp=Group
    Select grp.CopyToDataTable).ToArray
    

    Output of this LINQ will be array of datatable. In your case it will array containing 7 datatables.