LINQ Query Specific Data Retrieval

Help Robot
,
1

I have this excel file

image

Now using This LINQ query

dt.AsEnumerable.Where(Function(row) CDate(row(dateColumnName).ToString).Date.ToString(“dd-MM-yyyy”).Contains(date1)).CopyToDataTable

I am able to retrieve the records I wanted

What I want is while retrieving the records it should not consider those records where the Patch status is stated as cancelled

So how do I modify my above LINQ Query to achieve this ?

2

Hi @Ishan_Shelke1 ,

Try the Below Expression :

dt.AsEnumerable.Where(Function(row) CDate(row(dateColumnName).ToString).Date.ToString("dd-MM-yyyy").Contains(date1) andAlso Not(row("Patch Status").ToString.Trim.Equals("Cancelled"))).CopyToDataTable

Would also like to Suggest to Convert the Filtered Rows to an Array instead of using .CopyToDatatable.

Check the Below Post for Converting to List of DataRows and Convert to Datatable:

3

This worked well. What if there are no column names in my excel like my table looks like this

image

Then how would we modify the query ?

4

@Ishan_Shelke1 ,

We could with Column Indices or the Default Column Names when Add Headers is Disabled like Column1, Column2,...

5

Like can you modify this query of your own and tell me ?

dt.AsEnumerable.Where(Function(row) CDate(row(dateColumnName).ToString).Date.ToString(“dd-MM-yyyy”).Contains(date1) andAlso Not(row(“Patch Status”).ToString.Trim.Equals(“Cancelled”))).CopyToDataTable

6

@Ishan_Shelke1 ,

Considering the Patch Status is the First Column in Excel Sheet, we can use the Column Index as 0 and for the Date Column, the Column Index as 2.

So the Updated Query would be like below :

dt.AsEnumerable.Where(Function(row) Not(row(0).ToString.Trim.Equals("Cancelled")) andAlso CDate(row(2).ToString).Date.ToString("dd-MM-yyyy").Contains(date1)).CopyToDataTable
7

Worked Perfect Thanks

1 Like
8

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