Linq query to get mismatched data in list

Shilpa_Mohanty · January 23, 2025, 12:05pm

I have 2 dt
dt1(“taskwo”), dt1 can have multiple same taskwo with different status
dt2(“order number”)
taskwo in dt1 and order number in dt2 should be primary reference
i need to fetch all the data from dt1 which is not present in dt2
if dt1 has multiple taskwo and dt2 has same order number, check status in both case and return the 1 which has different status values

mkankatala · January 23, 2025, 12:12pm

Hi @Shilpa_Mohanty

Try using the below LINQ Expression in assign activity,

- Assign -> resultDT = (From row1 In dt1.AsEnumerable()
                        Group Join row2 In dt2.AsEnumerable()
                        On row1("taskwo").ToString() Equals row2("order number").ToString()
                        Into Group
                        From row2 In Group.DefaultIfEmpty()
                        Where row2 Is Nothing OrElse Not row1("status").ToString().Equals(row2("status").ToString(), StringComparison.InvariantCultureIgnoreCase)
                        Select row1).CopyToDataTable()

Hope it helps!!

Shilpa_Mohanty · January 23, 2025, 12:26pm

Hello Sir,

The above output is giving all data which are there in dt1

my ask is ,
dt1 can have 3 same taskwo but there status can be different
dt2 can have order number 1 of the 3 in dt1
so in that case we shd return the other 2 having different status than which is there in dt2

and all other data from dt1 which is not present in dt2 irrespective of the status values…

yedukondaluaregala · January 23, 2025, 12:51pm

Can you share sample input and output excel files if possible.
Note: you can put some sample data based on your condition

Shilpa_Mohanty · January 23, 2025, 12:54pm

sent you in text message

rkelchuri · January 23, 2025, 12:56pm

@Shilpa_Mohanty ,

Assign Activity for the Expression:

Create a variable filteredRows of type EnumerableRowCollection(Of DataRow), where you will store the filtered results.
In the Assign Activity, write the following LINQ expression:

vb

CopyEdit

filteredRows = dt1.AsEnumerable().Where(Function(row1) 
    dt2.AsEnumerable().Where(Function(row2) row1("taskwo").ToString() = row2("order number").ToString()).Any() = False OrElse 
    (dt2.AsEnumerable().Where(Function(row2) row1("taskwo").ToString() = row2("order number").ToString()).First()("status").ToString() <> row1("status").ToString())
).ToList()

Explanation:

dt1.AsEnumerable(): Converts dt1 into an enumerable collection so you can apply LINQ operations on it.
Where: Filters rows from dt1 based on the given condition.
Function(row1): Represents each row in dt1.
dt2.AsEnumerable(): Similarly, converts dt2 into an enumerable collection for comparison.
Where(Function(row2) …): Checks if there is a match between taskwo in dt1 and order number in dt2.
Any() = False: If no match is found for a taskwo, include that row from dt1.
First()(“status”) <> row1(“status”): If there is a match but the status is different, include the row from dt1.
ToList(): Converts the result into a list of filtered rows.

Result:

The filteredRows variable will now hold a collection of rows from dt1 that meet your criteria: either they don’t have a corresponding order number in dt2, or they have different status values.

Make sure that both dt1 and dt2 are correctly defined as DataTable variables in UiPath, and the expression will work as intended within the Assign activity.

yedukondaluaregala · January 23, 2025, 2:11pm

dt1.AsEnumerable().Where(Function(x) Not dt2.AsEnumerable().Any(Function(y) x(“TASKWO”).ToString() = y(“ParentWorkOrderNumber”).ToString() AndAlso y(“PreRequisiteStatus”).ToString() = x(“PREREQSTATUS”).ToString())).CopyToDataTable()

Can you try with this

Shilpa_Mohanty · January 23, 2025, 3:27pm

rkelchuri:

dt1.AsEnumerable().Where(Function(row1) 
    dt2.AsEnumerable().Where(Function(row2) row1("taskwo").ToString() = row2("order number").ToString()).Any() = False OrElse 
    (dt2.AsEnumerable().Where(Function(row2) row1("taskwo").ToString() = row2("order number").ToString()).First()("status").ToString() <> row1("status").ToString())
).ToList()

syntax error - multi lambda expression is missing ) expected

rkelchuri · January 23, 2025, 4:26pm

The error occurs due to mismatched parentheses in the LINQ expression. Let me correct it and simplify the syntax for UiPath’s Assign activity. Here’s the fixed LINQ expression:

vb

CopyEdit

filteredRows = dt1.AsEnumerable().Where(Function(row1) 
    Not dt2.AsEnumerable().Any(Function(row2) row1("taskwo").ToString() = row2("order number").ToString()) OrElse
    dt2.AsEnumerable().Any(Function(row2) row1("taskwo").ToString() = row2("order number").ToString() AndAlso row1("status").ToString() <> row2("status").ToString())
).ToList()

Explanation of Changes:

Not ... Any(...):

Ensures that rows in dt1 without a matching taskwo in dt2 are included.

Any(... AndAlso ...):

Finds rows where taskwo matches but status values differ.

Steps in UiPath:

Ensure dt1 and dt2 are declared as DataTable variables.
Create a variable filteredRows of type IEnumerable(Of DataRow) to store the filtered rows.
Use the above LINQ expression in the Assign Activity to filter dt1 based on your criteria.

If you need further clarification or assistance, let me know!

Shilpa_Mohanty · January 23, 2025, 4:49pm

rkelchuri:

dt1.AsEnumerable().Where(Function(row1) 
    Not dt2.AsEnumerable().Any(Function(row2) row1("taskwo").ToString() = row2("order number").ToString()) OrElse
    dt2.AsEnumerable().Any(Function(row2) row1("taskwo").ToString() = row2("order number").ToString() AndAlso row1("status").ToString() <> row2("status").ToString())
).ToList()

even though dt2 has all data in dt1, still the below query returns all dt2 data also

in_dtPhaseone.AsEnumerable().Where(Function(row1) Not dt_out.AsEnumerable().Any(Function(row2) row1(“TASKWO”).ToString() = row2(“PreReqWorkOrderNumber”).ToString()) OrElse (dt_out.AsEnumerable().Any(Function(row2) row1(“TASKWO”).ToString() = row2(“PreReqWorkOrderNumberr”).ToString()) AndAlso row1(“PREREQSTATUS”).ToString() <> dt_out.AsEnumerable().First(Function(row2) row1(“TASKWO”).ToString() = row2(“PreReqWorkOrderNumber”).ToString())(“PreRequisiteStatus”).ToString())).ToList()

supermanPunch · January 23, 2025, 5:16pm

Hi @Shilpa_Mohanty ,

Could you provide us with the sample data and the expected output for that data covering also the scenarios that you have mentioned ? So that we can conclude on the Linq or other methods to be suggested.

Shilpa_Mohanty · January 24, 2025, 4:40am

requirement is
compare both sheet
paste all data from sheet1 which is not present in sheet2 wrt TASKWO and
PreqREqWorkOrderNumber(irrespective of status)
if we find multiple instance of same TASKWO in sheet1, then paste the data which has different status compared to sheet2

Shilpa_Mohanty · January 24, 2025, 8:24am

hi there is a change in requirement,

here are 2 requirement

write query to get list of WO missing in dt1 → means populate all data from dt1 which are not there in dt2 after comparing TASKWO(column in dt1) and PreReqWorkOrderNUmber(column in dt2) (both these column have same data)
get all data from dt1 which has status difference in dt2 → means in dt2 whatever has status as WAPPR and in sheet1 it has different status, get that list

We need two datatables now…

rkelchuri · January 24, 2025, 8:40am

@Shilpa_Mohanty

Requirement 1: Get all data from `dt1` where `TASKWO` is not present in `dt2` (comparing `TASKWO` in `dt1` with `PreReqWorkOrderNumber` in `dt2`).

LINQ Expression:

vb

CopyEdit

missingWO = dt1.AsEnumerable().Where(Function(row1) 
    Not dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString())
).CopyToDataTable()

Requirement 2: Get all data from `dt1` where the `status` is different from the corresponding `status` in `dt2` (for matching `TASKWO` in `dt1` and `PreReqWorkOrderNumber` in `dt2`).

LINQ Expression:

vb

CopyEdit

statusDifference = dt1.AsEnumerable().Where(Function(row1) 
    dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
        row2("status").ToString() = "WAPPR" AndAlso row1("status").ToString() <> "WAPPR")
).CopyToDataTable()

Explanation of Each LINQ Query:

For Missing Work Orders (Requirement 1):

Not ... Any(...):

Ensures the TASKWO in dt1 doesn’t match any PreReqWorkOrderNumber in dt2.

.CopyToDataTable():

Converts the filtered rows into a new DataTable.

For Status Differences (Requirement 2):

Any(...):

Finds rows in dt2 where:
- TASKWO in dt1 matches PreReqWorkOrderNumber in dt2.
- status in dt2 is "WAPPR".
- status in dt1 is different from "WAPPR".

.CopyToDataTable():

Converts the filtered rows into a new DataTable.

UiPath Implementation:

Assign Activities:

Use two separate Assign Activities for missingWO and statusDifference with the above LINQ queries.

Variables:

dt1 and dt2: Input DataTable variables.
missingWO and statusDifference: Output variables of type DataTable.

If dt1 or dt2 has no rows matching the criteria, use a condition to avoid exceptions like The source contains no DataRows. For example:

vb

CopyEdit

If dt1.AsEnumerable().Where(...).Any() Then
    result = dt1.AsEnumerable().Where(...).CopyToDataTable()
Else
    result = dt1.Clone() ' Return an empty DataTable with the same structure
End If

Let me know if you need additional help with this!

If you find this is your solution, please mark it as Solution completed.

Shilpa_Mohanty · January 24, 2025, 9:11am

2nd datatable - take the dt2 and check what all has status as WAPPR. if the same order number in dt1 has wappr status then leave it . whatever has different status in dt1 whereas in dt2 has status as wappr should be returned

rkelchuri · January 24, 2025, 10:16am

@Shilpa_Mohanty

Got it! Here’s how we can modify the second query to meet your updated requirement:

We will:

Filter rows in dt2 where the status is WAPPR.
Check if the same order number (from PreReqWorkOrderNumber in dt2) exists in dt1.
Compare the status in both dt1 and dt2. If dt1 has a different status than WAPPR, include it in the result.

LINQ Expression (For Requirement 2):

vb

CopyEdit

statusDifference = dt2.AsEnumerable().Where(Function(row2) 
    row2("status").ToString() = "WAPPR" AndAlso 
    dt1.AsEnumerable().Any(Function(row1) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
        row1("status").ToString() <> "WAPPR")
).CopyToDataTable()

Explanation:

Filter dt2:

Use .Where(Function(row2) row2("status").ToString() = "WAPPR") to get rows from dt2 where the status is WAPPR.

Check in dt1:

Use .Any(Function(row1) to find if the same PreReqWorkOrderNumber from dt2 exists as TASKWO in dt1.
If it exists, compare the status of TASKWO in dt1 with WAPPR. Only include rows where the status in dt1 is different.

CopyToDataTable:

Converts the filtered rows into a new DataTable for further processing.

Example Implementation in UiPath:

Input DataTables:

dt1 (columns: TASKWO, status).
dt2 (columns: PreReqWorkOrderNumber, status).

Assign Activities:

Assign for statusDifference:

vb

CopyEdit

statusDifference = dt2.AsEnumerable().Where(Function(row2) 
    row2("status").ToString() = "WAPPR" AndAlso 
    dt1.AsEnumerable().Any(Function(row1) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
        row1("status").ToString() <> "WAPPR")
).CopyToDataTable()

Variable Types:

dt1 and dt2: Input DataTable variables.
statusDifference: Output DataTable variable.

Handling No Matching Rows:

If no rows match the criteria, CopyToDataTable() will throw an error. Handle it like this:

vb

CopyEdit

If dt2.AsEnumerable().Where(Function(row2) 
    row2("status").ToString() = "WAPPR" AndAlso 
    dt1.AsEnumerable().Any(Function(row1) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
        row1("status").ToString() <> "WAPPR")
).Any() Then
    statusDifference = dt2.AsEnumerable().Where(Function(row2) 
        row2("status").ToString() = "WAPPR" AndAlso 
        dt1.AsEnumerable().Any(Function(row1) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
            row1("status").ToString() <> "WAPPR")
    ).CopyToDataTable()
Else
    statusDifference = dt2.Clone() ' Create an empty DataTable with the same structure
End If

This ensures no exceptions are raised if there are no matching rows.

Let me know if this works for your scenario!

Shilpa_Mohanty · January 24, 2025, 11:36am

rkelchuri:

statusDifference = dt2.AsEnumerable().Where(Function(row2) 
    row2("status").ToString() = "WAPPR" AndAlso 
    dt1.AsEnumerable().Any(Function(row1) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
        row1("status").ToString() <> "WAPPR")
).CopyToDataTable()

req1- missingWO = dt1.AsEnumerable().Where(Function(row1)
Not dt2.AsEnumerable().Any(Function(row2) row1(“TASKWO”).ToString() = row2(“PreReqWorkOrderNumber”).ToString())
).CopyToDataTable()

req1 is not giving if any duplicate is there in dt1

req 2-
statusDifference = dt2.AsEnumerable().Where(Function(row2)
row2(“status”).ToString() = “WAPPR” AndAlso
dt1.AsEnumerable().Any(Function(row1) row1(“TASKWO”).ToString() = row2(“PreReqWorkOrderNumber”).ToString() AndAlso
row1(“status”).ToString() <> “WAPPR”)
).CopyToDataTable()

req2 - it should return the value from dt1 but returning the value form dt2

rkelchuri · January 24, 2025, 12:08pm

@Shilpa_Mohanty

Issue 1: `req1` is not handling duplicates in `dt1`

If dt1 has duplicate rows with the same TASKWO, we should ensure all such rows are included in the result, even if they exist multiple times. Here’s the corrected LINQ query:

vb

CopyEdit

missingWO = dt1.AsEnumerable().Where(Function(row1)
    Not dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString())
).CopyToDataTable()

Fix for duplicates:

The above query already handles duplicates in dt1. If you’re encountering issues, the problem might be due to an empty dt2 or unexpected data in the tables. To ensure robustness:

Handle the case where dt1 or dt2 might be empty:

vb

CopyEdit

If dt1.AsEnumerable().Where(Function(row1)
    Not dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString())
).Any() Then
    missingWO = dt1.AsEnumerable().Where(Function(row1)
        Not dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString())
    ).CopyToDataTable()
Else
    missingWO = dt1.Clone() ' Empty DataTable with the same structure
End If

Issue 2: `req2` returns rows from `dt2` instead of `dt1`

This is because the query is filtering rows from dt2. To return rows from dt1, we need to switch the primary DataTable being iterated and adjust the logic. Here’s the corrected query:

vb

CopyEdit

statusDifference = dt1.AsEnumerable().Where(Function(row1)
    dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
        row2("status").ToString() = "WAPPR" AndAlso row1("status").ToString() <> "WAPPR")
).CopyToDataTable()

Explanation of Changes:

Start with dt1.AsEnumerable():

This ensures that the result contains rows from dt1.

Check against dt2:

Use .Any() to find matching rows in dt2 where:
- TASKWO matches PreReqWorkOrderNumber.
- The status in dt2 is "WAPPR".
- The status in dt1 is not "WAPPR".

Robustness (Handle Empty `dt1` or `dt2`):

vb

CopyEdit

If dt1.AsEnumerable().Where(Function(row1)
    dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
        row2("status").ToString() = "WAPPR" AndAlso row1("status").ToString() <> "WAPPR")
).Any() Then
    statusDifference = dt1.AsEnumerable().Where(Function(row1)
        dt2.AsEnumerable().Any(Function(row2) row1("TASKWO").ToString() = row2("PreReqWorkOrderNumber").ToString() AndAlso 
            row2("status").ToString() = "WAPPR" AndAlso row1("status").ToString() <> "WAPPR")
    ).CopyToDataTable()
Else
    statusDifference = dt1.Clone() ' Empty DataTable with the same structure
End If

Summary of Fixes:

req1:

The query handles duplicates if dt1 has them.
Ensure that dt1 or dt2 isn’t empty to avoid runtime errors.

req2:

Adjusted to return rows from dt1 by iterating through dt1.AsEnumerable() and comparing with dt2.
Added robustness to handle cases where no matches are found.

Let me know if you encounter further issues!

Shilpa_Mohanty · January 24, 2025, 12:41pm

why these vb never work in assign activity
always showing ( expected

Shilpa_Mohanty · January 27, 2025, 9:18am

Can you help
its a bit urgent

Topic		Replies	Views
Linq query for comparing 2 datatables Activities excel , activities , question	8	2030	July 6, 2023
Compare two datatable Write Status Studio studio , question , activities_panel	16	410	September 28, 2023
How to look multiple columns Activities excel , activities , question	9	73	February 11, 2025
Compare 2 dataTable according to 1 specific column and take the rows which has different values Activities excel , activities , question	17	2185	August 7, 2021
Ho to check compare two Datatable and remove matching from one datatable Studio studio , question , activities_panel	3	31	January 10, 2025

Linq query to get mismatched data in list

Explanation:

Result:

Explanation of Changes:

Steps in UiPath:

Requirement 1: Get all data from dt1 where TASKWO is not present in dt2 (comparing TASKWO in dt1 with PreReqWorkOrderNumber in dt2).

LINQ Expression:

Requirement 2: Get all data from dt1 where the status is different from the corresponding status in dt2 (for matching TASKWO in dt1 and PreReqWorkOrderNumber in dt2).

LINQ Expression:

Explanation of Each LINQ Query:

For Missing Work Orders (Requirement 1):

For Status Differences (Requirement 2):

UiPath Implementation:

LINQ Expression (For Requirement 2):

Explanation:

Example Implementation in UiPath:

Handling No Matching Rows:

Issue 1: req1 is not handling duplicates in dt1

Fix for duplicates:

Issue 2: req2 returns rows from dt2 instead of dt1

Explanation of Changes:

Robustness (Handle Empty dt1 or dt2):

Summary of Fixes:

Related topics

Requirement 1: Get all data from `dt1` where `TASKWO` is not present in `dt2` (comparing `TASKWO` in `dt1` with `PreReqWorkOrderNumber` in `dt2`).

Requirement 2: Get all data from `dt1` where the `status` is different from the corresponding `status` in `dt2` (for matching `TASKWO` in `dt1` and `PreReqWorkOrderNumber` in `dt2`).

Issue 1: `req1` is not handling duplicates in `dt1`

Issue 2: `req2` returns rows from `dt2` instead of `dt1`

Robustness (Handle Empty `dt1` or `dt2`):