Filtering Multiple Column using LinQ where one of the columns must have a null value

Help Activities
, ,
1

Good Day.

I am trying to filter this excel file or datatable using LINQ and paste it on another sheet (Sheet2) where the Occupation must be Enforcer and Architect only and the Code Number must be blank or null.

This is the working excel file:
SampleExcelFile.xlsx (186.4 KB)

This is the desired output:

image
image360×834 22.2 KB

I am trying to use this script but it shows an error:
datatable.AsEnumerable.Where(Function(row) (row(“Occupation”).ToString.Equals(“Architect”) OR row(“Occupation”).ToString.Equals(“Enforcer”)) AndAlso row.IsNull(“Code Number”)).CopyToDataTable

May I know how can I solve this? Thank you so much.

Best regards,
Robert Russell Monsalud

2

@RobertRussell_Monsalud

Please try this

datatable.AsEnumerable.Where(Function(r) {"Architect","Enforcer"}.Contains(r("Occupation").ToString) AndAlso (IsNothing(r("Code Number")) OrElse String.IsNullOrEmpty(r("Code Number).ToString.Trim))).CopyToDataTable

Hope this helps

Cheers

1 Like
3

Also give this a try:

If(datatable.AsEnumerable().Where(Function(row) _
    (row.Field(Of String)("Occupation") = "Architect" OrElse row.Field(Of String)("Occupation") = "Enforcer") AndAlso _
    (row.IsNull("Code Number") OrElse String.IsNullOrWhiteSpace(row.Field(Of String)("Code Number")))).Any(), _
    datatable.AsEnumerable().Where(Function(row) _
        (row.Field(Of String)("Occupation") = "Architect" OrElse row.Field(Of String)("Occupation") = "Enforcer") AndAlso _
        (row.IsNull("Code Number") OrElse String.IsNullOrWhiteSpace(row.Field(Of String)("Code Number")))).CopyToDataTable(), _
    datatable.Clone())

filters the datatable based on the conditions provided (Occupation is “Architect” or “Enforcer”, and Code Number is null or blank). It also handles the case where the filter results might be empty by using datatable.Clone() to create an empty DataTable with the same structure

1 Like
4

Thank you for the help. I really appreciate it @SenzoD @Anil_G

1 Like
5

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