Linq Query for filtering two Datatables based on selected columns

Help Studio
, ,
1

I want to filter common data rows based on some columns in below attatched example data tables. Help me to write a linq query or suggest other way.
First Datatable Image

image
image1342×89 8.55 KB

Second Datatable Image
image
image840×523 30.5 KB

Note- Here i want to write a linq query which will give the matched result after comparing the columns of- ‘Start date’, ‘End date’ from First Datatable and ‘Date’ from second Datatable.

2

@ankita.rai

Try this

Dt2.AsEnumerable.Where(function(x) dt1.AsEnumerable.Any(function(y) Cdate(y("Start Date").ToString) < Cdate(x("Date").Tostring) AndAlso Cdate(y("End Date").ToString) > Cdate(x("Date").Tostring))).CopyToDatatable

This checks if date is between strt and end date

If you need anything else change the conditions accordingly

Cheers

3

@ankita.rai Below are the sample file, used data table 1

image

Below is data from Table 2

image
image292×147 1.83 KB

var matchedDates = table2.AsEnumerable().Where(row2 =>
            table1.AsEnumerable().Any(row1 =>
                DateTime.Parse(row1["Start date"].ToString()) <= DateTime.Parse(row2["Date"].ToString()) &&
                DateTime.Parse(row1["End date"].ToString()) >= DateTime.Parse(row2["Date"].ToString())));

Output we received
It return the 4th dep name

image

@ankita.rai I have attached the input file for my output.

datatable1.zip (372 Bytes)

4

@Anil_G , Thankyou for the quick reply, can you update the query to copy mismatched result?

5

Have a look on LINQ in general and get trained the main building blocks
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

Assign Activity:
dtUnmatched =

Dt2.AsEnumerable.Where(function(x) Not dt1.AsEnumerable.Any(function(y) Cdate(y("Start Date").ToString) < Cdate(x("Date").Tostring) AndAlso Cdate(y("End Date").ToString) > Cdate(x("Date").Tostring))).CopyToDatatable

we just denied the Any

6

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