CODEWITHSUNDEEP
linqdatatable
A G
A G

Reputation: 22587

LINQ Remove items from collection if not in datatable

I have a collection in which each each item has a property "ID". There is a datatable which has a column "ID".

If there is any value in collection that is not in the datatable, it needs to be removed.

Can it be done with LINQ or the collection needs to be iterated?

Upvotes: 1

Views: 2336

Answers (4)

onof
onof

Reputation: 17367

If you want a collection of DataRow's:

dataTable.Rows.Cast<DataTable>
      .Where(dr => myCollection.Find(s => s.ID.Equals(dr["ID"])) != null)

else, if you need to filter the collection of your objects:

   var ids =  dataTable.Rows.Cast<DataTable>.Select(dr => Convert.ToInt32(dr["ID"]));
   var filteredCollection = myCollection.Where(s => ids.Contains(s.Id));

If you add a reference to System.Data.DataSetExtensions, you can shorten it:

   var ids = dataTable.AsEnumerable().Select(dr => dr.Field<int>("ID"));
   var filteredCollection = myCollection.Where(s => ids.Contains(s.Id));

Upvotes: 2

Neil T.
Neil T.

Reputation: 3320

You don't want to do this, because due to the change tracking mechanism in the LINQ data context, there is no guarantee that the information contained within the collection is still going to be in the database between query requests.

Iterating through the collection will be a waste of time...you are much better off re-executing the query to repopulate the collection.

var query = dc.Collection.Where(c => (dc.DataTable.Select(dt => dt.Customer_ID)).Contains(c.Customer_ID));

Upvotes: 0

Ahmed Magdy
Ahmed Magdy

Reputation: 6040

Here is an example but I didn't test it

var items = new List<Item>();
var dataTable = new DataTable { Columns = { new DataColumn("ID", typeof(int)) } };
var rows = dataTable.AsEnumerable();
var itemsToRemove = items.Join(rows, item => item.ID, row => row.Field<int>("ID"), (item, row) => item);
foreach (var item in itemsToRemove)
{
    items.Remove(item);
}

I hope it helps u. it's based on joining both based on the id.

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1502246

LINQ itself is about performing a query, not changing a collection.

Two options:

  • Create a new collection using LINQ using only the appropriate values from the original collection. Replace the original collection with this. (Not so easy if there are multiple references to the original collection.)
  • Use LINQ to create a collection of values to remove from the original collection, and then iterate over that separate collection, removing elements from the original collection. Note that you should materialize this "collection of values to remove" (e.g. with a call to ToList) as otherwise you'll still be iterating over the original collection when you remove items from it, which will lead to an exception.

I don't know what datatables do in the way of indexing, but if the table is large you might want to create a HashSet of IDs to start with, e.g.

var query = dataTable.AsEnumerable()
                     .Field<string>("ID"); 
HashSet<string> dataTableIds = new HashSet<string>(query);

That way you can check for containment very cheaply.

Upvotes: 1

Related Questions