Learning LINQ: Remove Rows based on values from two columns

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

@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

@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!

@JosephNehl
Perfect, happy automation :+1:

1 Like

@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.


Error3

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

try updated statement again

2 Likes

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!

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