Compare DataRow collection to List<T>

Question

I have a List<string> and I have a DataTable.

One of the columns in a DataRow is ID. The List holds instances of this ID.

The DataTable gets populated on a Timer.

I want to return items from the List that are not in the DataTable into another list.

Answer 1

You will want to do something like this

var tableIds = table.Rows.Cast<DataRow>().Select(row => row["ID"].ToString());

var listIds = new List<string> {"1", "2", "3"};

return listIds.Except(tableIds).ToList();

You can cast the rows in the data table to be an IEnumerable collection and then select the "ID" column value from each of them. You can then use the Enumerable.Except extension method to get all of the values from the List that are not in the collection you just made.

If you need to get the values that are in the table but not the list, just reverse listIds and tableIds.

Answer 2

If your table was something like that:

DataTable dt = new DataTable();
dt.Columns.Add("ID");
DataRow dr = dt.NewRow();
dt.PrimaryKey = new DataColumn[] {dt.Columns[0]};
dr["ID"] = "1";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["ID"] = "2";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["ID"] = "3";
dt.Rows.Add(dr);

and the list was something like this:

List<string> ls = new List<string>{"1","2","4"};

we could get the items found in the list and not in the datatable this way:

var v = from r in ls
                where !dt.Rows.Contains(r)
                select r;
        v.ToList();

Answer 3

With reasonable efficiency via HashSet<T> (and noting that the fastest way to get data out of a DataRow is via the DataColumn indexer):

        HashSet<int> ids = new HashSet<int>();
        DataColumn col = table.Columns["ID"];
        foreach (DataRow row in table.Rows)
        {
            ids.Add((int)row[col]);
        }
        var missing = list.Where(item => !ids.Contains(item.ID)).ToList();