Using Linq to delete rows from datatable based on values in a list

[mbuehler (MB)]

I have the current workflow that works, but think a linq statement could possibly do it faster.
I have to compare a each row in a column named “File Name” to check if it’s value contains a value found in list_ExFileNames. If it does, then the row needs to be deleted from dt_Report.

My current workflow checks each row of the data table for each item in the list and if it does it replaces the value with “Delete Row”. After this activity is done, I filter to remove all rows with “Delete Row” Values.

The list has about 14 items on it and the reports can have hundreds of rows.

[Shiva_Nikhil]

FinalDT = SourceDT.AsEnumerable.Where(Function(row) Not List.Contains(row(“File Name”).ToString)).CopyToDataTable

List is the name of the variable, sourcedt is the name of the datatable.

Hope this will helps

[Quenton_Wayne_Rebello]

Hey @mbuehler,
You can try using

dt.AsEnumerable.ToArray.Except(dt.AsEnumerable.Where(Function(row) inputList.Contains(row("File Name").ToString)).ToArray).CopyToDataTable 

Or

dt.AsEnumerable.Except(dt.AsEnumerable.Where(Function(row) inputList.Contains(row("File Name").ToString)), System.Data.DataRowComparer.Default).CopyToDataTable 

[mbuehler (MB)]

thanks for the reply, however this seems to just remove the first instance of each item in the list if it exists instead of removing every instance of it in the table. I had a similar result on my own when I tried linq and couldn’t sort out why it was doing this.

[Shiva_Nikhil]

Can you explain what was the issue you are facing,

Or Is there any linq you required

[Anil_G (Anil Gorthi)]

@mbuehler

Please try this

Dt.AsEnumerable.Where(function(x) Not Listvar.Any(function(y) x("FileName").ToString.Contains(y))).CopyToDataTable

Hope this helps

Cheers

[mbuehler (MB)]

@Anil_G
Thanks, however this is populating the new datatable with all the rows that I want deleted instead of deleting these rows and giving me everything else. I will see if I can manipulate this statement to work for me when I have more time.

[Anil_G (Anil Gorthi)]

@mbuehler

If that is the case then in the statement just remove the Not and it would work the other way round

Cheers

[mbuehler (MB)]

@Anil_G -Turns out I must have somehow deleted the Not when I initially copied it over and updated the list variable. I added it back in and it is working as needed. Thank you.