Filter DataTable using Linq

I need to filter my Datatable using Linq as there is large data in excel. Its taking too much time to filter all the data.
image

Can someone help me with the Linq logic for the above code.
@ppr @Raghavendraprasad @ClaytonM @supermanPunch @sarathi125 @Yoichi @arivu96
@PrankurJoshi @indra @poorna_nayak07 @Palaniyappan @Nithinkrishna @NIVED_NAMBIAR

Hi @Satish_Ch

could you share us the expected input and output

1 Like

Hi I just want to filter column 2 “TrailerType” .I want ony rows with values not blanks.
image

Once i filter with column2. Column 3 is for datetime so there i am facing issues while inserting null values into db so i tried adding to datatable with if condition .
Please refer screenshots from topic.
Could anyone help me as its urgent.

blocking blanks in only one col:

dtFiltered =
(From d in YourDataTableVar.AsEnumerable
Where Not (isNothing(d(“TrailerType”) OrElse String.isNullorEmpty(d(“TrailerType”).toString.Trim))
Select r = d).CopyToDataTable

1 Like

blocking blanks in any col:

dtFiltered =

(From d in YourDataTableVar.AsEnumerable
Where Not d.ItemArray.Any(Function (x) isNothing(x) OrElse String.isNullorEmpty(x.toString.Trim))
Select r = d).CopyToDataTable

Hi @ppr ,

Thanks for quick response.
I tried this logic its working fine for filtering data. But there is problem while uploading the same data to DB as 3rd column(variable type - DateTime) has null values, so i was considering it as an object while build datatable in my for loop logic and with that approach i was not facing any issue for upload datatime with null values which basically shown as “” in debug mode. Could you suggest me the additional logic for the same linq query.
please find attached screenshot for the error i am recieving now.
please find screenshots in topic for logic i implemented earlier.

Thanks in Advance :slight_smile:

How to maintain column1 as string,column2 as string, column3 as object and column 4 as string in the same linq while filtering.
May be that would solve my problem.

so LINQ filter is working

in our understanding this is another case. Filtering is working, so now you are looking on how to modify / preparing data for the uploading for empty dates

We would suggest to scope 1 topic = 1 case

give a try in a RnD if a nothing / DBNull.Value would work instead of empty string

how to modify / preparing data for the uploading for empty dates

Yes.Correct.
I tried with dbnull.value its not working.

Its not only with null values in datetime type column even the format of dates should match with sql table.

If we can add column3 datetime as object and convert values into correct datetime format. Then it will work.

Ok, but is not a filter case as origin this Topic was titled.

Filtering should work. So close this topic and open a new one with this new case. When scoping 1 topic = 1 case you help other researchers to find faster solutions for similiar cases. Thanks for support

1 Like

Okay sure @ppr

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