Filter few columns with LINQ

Hi, there,

I am trying to filter the records from few columns of DT which have value as Not-Found, but unfortunately, I am not getting the accurate result. I want to get all the data which has value as Not-Found from few columns., below is the LINQ I wrote but, its not working as expected, can’t figure out the issue

(From d In dt_JSONheader.AsEnumerable
Where d("Supplier.Name").ToString.Trim.Equals("Not-Found") AndAlso d("Supplier.smVendorID").ToString.Trim.Equals("Not-Found") AndAlso d("AgreementDate").ToString.Trim.Equals("Not-Found") AndAlso d("ExpirationDate").ToString.Trim.Equals("Not-Found")
Select d).ToList

sample input data
Sample.xlsx (13.5 KB)

@indrajit.shah Try with contains
please provide some dummy data.

it’s the same, I am not getting all the records where value is Not-Found for the below columns, its giving me combined result, whereas I need all the records having not-Found value under these below columns.
Supplier.Name Supplier.smVendorID AgreementDate ExpirationDate

@indrajit.shah
It is case sensitive issue. please convert column values to lower or to upper then compare.

like this.

Where d(“Supplier.Name”).ToString.ToLower.Trim.Equals(“not-found”) AndAlso …

Hi @indrajit.shah

You can try with filter DataTable activity

Check out the XAML file

ChecktheExcelData.xaml (7.5 KB)

image

image

Regards
Gokul

the value is a hard-coded value, and also, the count is not accurate, I am looking for all the records where values are not-found under those 4 columns, but the result is not correct.

below is my updated LINQ

(From d In dt_JSONheader.AsEnumerable
Where d("Supplier.Name").ToString.ToUpper.Trim.Contains("NA") AndAlso d("Supplier.smVendorID").ToString.ToUpper.Trim.Contains("NA") AndAlso d("AgreementDate").ToString.ToUpper.Trim.Contains("NA") AndAlso d("ExpirationDate").ToString.ToUpper.Trim.Contains("NA")
Select d).ToList

Hi @indrajit.shah please try with filter data table activity and use contains instead of equals

Thank you @Gokul001 , but I want to avoid using this activity as when processing large number of data, the results are not correct, that’s why I switch to LINQ.

@indrajit.shah can you give us excel file.

I have attached with the main topic.

No @indrajit.shah , It will give the correct out every time in the Filter data table.

Both Linq and Filter activity do the same. Just Try with the activity.

Based on the input Dt count is 246 and after filtering the dtOutput count is 131

Regards
Gokul

okay, but I had a very bad experience with this activity, now the data is 246 it’s just a sample, but I will be processing 400K+ data.

It will work if you have 1000000K+ data in the filter activity. Then how do you know LINQ will working if it have more data? It a myth @indrajit.shah

@indrajit.shah
you are using andelse
which means you are getting all that data where all columns contains Not-Found, but in your case all columns not containing “Not-Found”.

(From d In dt_JSONheader.AsEnumerable
Where d(“Supplier.Name”).ToString.ToUpper.Trim.Contains(“NA”) OrAlso d(“Supplier.smVendorID”).ToString.ToUpper.Trim.Contains(“NA”) OrAlso d(“AgreementDate”).ToString.ToUpper.Trim.Contains(“NA”) OrAlso d(“ExpirationDate”).ToString.ToUpper.Trim.Contains(“NA”)
Select d).ToList

Hi @indrajit.shah

How about this LINQ expression?

Instead of AndAlso try with Or

DtRead.AsEnumerable.Where(Function(r) r("Supplier.Name").ToString.Trim.Contains("Not-Found") or r("Supplier.smVendorID").ToString.Trim.Contains("Not-Found") or r("AgreementDate").ToString.Trim.Contains("Not-Found") or r("EffectiveDate").ToString.Trim.Contains("Not-Found")).CopyToDataTable

Regards
Gokul

lets have a look on the task

Mapped to your sample data we can summarize the Requirement:

  • filter all rows where value “Not-Found” is present in one or more datacolumn values

As mentioned already by others when working with Filter Datatable or close to your statement statement style it is a OR conjuction needed.

When we want to do it with a LINQ we can reduce redundant code parts and also make it more robust like (assumptions: simple datacolumn datatypes, no empty filter result expected)

(From d in dtData.AsEnumerable
Where d.ItemArray.Any(Function (x) x.ToString.ToUpper.Trim.Equals("NOT-FOUND")
Select r = d).CopyToDataTable

when a dedicated mentioning of the filter col names is preferred we can do:

(From d in dtData.AsEnumerable
Let cols = new String(){"Supplier.Name","Supplier.smVendorID","AgreementDate", "ExpirationDate"}
Where cols.Any(Function (c) d(c).ToString.ToUpper.Trim.Equals("NOT-FOUND")
Select r = d).CopyToDataTable

or with index

(From d in dtData.AsEnumerable
Where {0,1,2,3}.Any(Function (c) d(c).ToString.ToUpper.Trim.Equals("NOT-FOUND")
Select r = d).CopyToDataTable

But as show cased the redundant parts of trimming / toUpper… are avoided. Shifting to a contains check is also possible.

When empty results are to expect we do handle:
🚑 🆘 [FirstAid] Handling of The source contains no DataRows exception

About Filter approach options
About the options of filter a datatable Filter DataTable, Select, LINQ (there is also another variation available) the Blog was shared with you.

We would prefer to avoid the different discussions where we often do see missinterpretated facts used for argumentations or ignored facts isolated in too close scoped viewpoints.

Based on facts and clear on approach rating criteria sets we can define a rule:
Whenever the left side column value is to prepare/modify/convert… for the filtering THEN using filter datatable has a risk of not being the preferred approach.

As we feel that making the Bot more robust when trimming and toUpper case transforming the column value we do hit this rule

1 Like

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