Learning LINQ: Remove Rows based on values from two columns

Help
, ,
1

Hello All,

I am in the process of learning LINQ and with searching forums have come up with the following query to try and remove rows from a data table based on the values from two columns.

in_Dt = in_Dt.AsEnumerable().where(Function(x) Not (x("Location")).Equals(row("Location").ToString) And Not (x("Date").ToString).Equals(row("Date").toString).CopyToDataTable

This above code is being run in a for each row loop and the row(“Location”) and row(“Date”) are from a datatable with rows combinations that should be removed from my larger table.

But this isn’t functioning as I intended. It is removing all the rows with the values of that location, not just the values of that specific location+date combined. I only want rows with that certain combination of both date and location to be removed, leaving any combinations of that location+other dates.

If someone could help me arrange this so that it takes both columns into consideration when removing a row that would be greatly appreciated! Thank you all!

Here is a quick example table of what I am expecting
Capture

2

@JosephNehl
Your case looks like that all rows from removal table should be removed from orig data when found (Criteria: same Location, same date)

if the tables are only 2 cols long then we can try to do it with set operation and except. Have a look here:
DataTable_SetOperations.xaml (8.9 KB)

in your case:
dtDataOrig.AsEnumerable.Except(dtRemoval.AsEnumerable, DataRowComparer.Default).CopyToDataTable

if more cols are present then give a try on:

assign activity (single isolated, no within a for each / for each row)
left side: dtResult (DataTable)
right side:

(From d In dtOrig.AsEnumerable()
Where Not dtRemoval.AsEnumerable.Any(Function (x) x("Location").toString.Equals(d("Location").ToString) And x("Date").ToString.Equals(d("Date").toString))
Select d).CopyToDataTable

EDIT:
Also check on how the date is handled / transported from Excel to the datatable.
Even when it looks in EXCEL as MM/dd/yyyy it can be the case that it is recognized as MM/dd/yyyy HH:mm:ss. The it can fail on the time part (today morning is different from today evening including time, but the same excluding time)

Analysis this: set a break point and debug, inspect the content of the datatable

Fixing:

  • Quick dirty: Substring(0,10) on date string
  • Quality Fix: datetime.parseExact and compare on dateTimeParsed.Date
1 Like
3

@ppr Thank you for your response! It is the case that I have multiple extra columns in the original table so I appreciate the extra effort of giving those two options. Thank you for the tidbit about the Datetime as well! I will double check my workflows and make sure that all of that has been implemented properly.

I will try this out, my workflow will need to rearranged slightly to fit this answer as I simplified the question a bit to not include some logic that could cause this to become even more messy. I believe if I do it the way you have highlighted however it will be a lot better in the end. I will let you know if I run into any further issues. Thank you again!

4

@JosephNehl
Perfect, happy automation :+1:

1 Like
5

@ppr Sorry for the late response, Things have been busy so I didn’t get a chance to try out the LINQ until just a bit ago. I am unfortunately running into an error regarding the “x” variable and the “.Any” as you can see attached below.

Error2
Error21919×1041 89.7 KB

Error3

Thank you for any insight you can give in regards to this!

6

try updated statement again

4 Likes
7

Perfect, Thank you! That got rid of the error. It is now functioning fully and as expected! Thank you so much again for your help!

8

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