Enumerate over DataTable, filter items, then revert to DataTable

Question

I'd like to filter items in my DataTable by whether a column value is contained inside a string array by converting it to an IEnumerable<DataRow>, afterwards I'd like to re-convert it to DataTable since that's what my method has to return.

Here's my code so far:

string[] ids = /*Gets string array of IDs here*/
DataTable dt = /*Databasecall returning a DataTable here*/
IEnumerable<DataRow> ie = dt.AsEnumerable();
ie = ie.Where<DataRow>(row => ids.Contains(row["id"].ToString()));
/*At this point I've filtered out the entries I don't want, now how do I convert this back to a DataTable? The following does NOT work.*/
ie.CopyToDataTable(dt, System.Data.LoadOption.PreserveChanges);
return dt;

well i had got my answer, should have deleted the comment, sorry. — Orkun, Feb 27 '12 at 16:40
But _still_ that first parameter is meant to be the _source_ ...i think you need dt to be assigned to the _result_ .. (am i missing smth here?) — Orkun, Feb 27 '12 at 16:46
sorry but you are wrong: http://msdn.microsoft.com/en-us/library/bb396189.aspx .. see the _parameters_ section and note that its called _source_ — Orkun, Feb 27 '12 at 16:49
It's an extension method, of course the first parameter is the source, but the first parameter is also the `IEnumerable`, not the `DataTable`, you will see that the `DataTable` actually is the destination. — Dennis Röttger, Feb 27 '12 at 16:51

Enrico Campidoglio · Answer 1 · 2012-02-27T16:36:24.833

1

Assuming that you want to filter the rows in-place, that is the filtered rows should be returned in the same DataTable that was created through the original database query, you should first clear the DataTable.Rows collection. Then you should copy the filtered rows to an array and add them sequentially:

ie = ie.Where<DataRow>(row => ids.Contains(row["id"].ToString())).ToArray();
dt.Rows.Clear();

foreach (var row in ie)
{
    dt.Rows.Add(row);
}

An alternative way to achieve this could be to simply iterate through the rows in the DataTable once and delete the ones that should be filtered out:

foreach (var row in dt.Rows)
{
    if (ids.Contains(row["id"].ToString()) == false)
    {
        row.Delete();
    }
}

dt.AcceptChanges();

Note that if the DataTable is part of a DataSet that is being used to update the database, all modifications made to the DataTable.Rows collection will be reflected in the corresponding database table during an update.

edited Feb 27 '12 at 16:36

answered Feb 27 '12 at 16:15

Enrico Campidoglio

56,676
12
126
154

1

When I clear my DataTable, my IEnumerable gets cleared too. When I iterate over each row and delete it inside the ForEach Loop, I will receive an error because I changed a collection during the iteration (logically). Using for loops or creating a 2nd DataTable surely are options, but I'm looking for something a little less dirty. – Dennis Röttger Feb 27 '12 at 16:16
@DennisRöttger Why do you think that creating a new tabel is dirtier than mutating an existing one? – Rune FS Feb 27 '12 at 16:29
@DennisRöttger Correct. I modified my example to use the [DataRow.Delete()](http://msdn.microsoft.com/en-us/library/system.data.datarow.delete.aspx) method instead, which won't take effect until you call [DataTable.AcceptChanges()](http://msdn.microsoft.com/en-us/library/system.data.datarow.acceptchanges.aspx). – Enrico Campidoglio Feb 27 '12 at 16:33
I think it just feels natural to convert data of an object you want to filter to a type that allows filtering, do your thing and then convert it back to the object the data originally "came from", wouldn't you agree? – Dennis Röttger Feb 27 '12 at 16:33
@DennisRöttger I also corrected my first example by copying the sequence of rows to filter to an array. – Enrico Campidoglio Feb 27 '12 at 16:38

score 1 · Accepted Answer · answered Feb 27 '12 at 16:22

1

I would create an empty clone of the data table:

DataTable newTable = dt.Clone();

Then import the rows from the old table that match the filter:

foreach(DataRow row in ie)
{
    newTable.ImportRow(row);
}

answered Feb 27 '12 at 16:22

Joe

122,218
32
205
338

That's what I resorted to doing now too, I hoped for a more elegant solution which utilizes the original DataTable. – Dennis Röttger Feb 27 '12 at 16:26

Enumerate over DataTable, filter items, then revert to DataTable

2 Answers2