How do I get a list of integers that exist in one collection, but not in another of a different type using LINQ?

Question

I have two different collections:

List<int>
IEnumerable<IDataRecord>

The first contains a list of primary keys. The second contains data records. What I would like to do is determine which primary keys in the first collection do not exist in the second based on a given column name and return them as a new collection.

For example, if I have this:

private List<int> _currentKeys = new List<int>();

public void Update(DbDataReader reader, string keyColumn) {
    var records = reader.AsEnumerable(); // my own extension method
    var difference = // LINQ stuff here
}

Say _currentKeys contains [1, 2, 3, 4, 5] and reader contains records with the values [1, 4, 5] in a column called ID. If I call Update, I need to get a collection containing [2, 3].

As always, I have a feeling that this is super simple, but the fact that the two collections are different types completely threw me off. Combined with the fact that I haven't used LINQ much yet.

Answer 1

The trick is to query the Primary Key list for keys that your reader does not contain. The where statement specifies which column in the reader to compare. They do have to be of the same data type, however.

List<int> _currentKeys = new List() { 1, 2, 3, 4, 5);

List<int> NotFound = (
    from pk in _currentKeys
    where !reader.Select(rec => rec.MyFieldToCompare).Contains(pk)
    select pk).ToList();

If they aren't of the same data type, just convert the in memory collection to match the type of the DB collection. IE if your primary key is saved as a varchar in the database, you'd covert _currentKeys like this:

List<string> _currentKeysStr = 
    Array.ConvertAll<int, string>
        (_currentKeys.ToArray(), Convert.ToString).ToList();

Answer 2

You could do this:

var difference = _currentKeys.Where(k => !records.Any(r => (int)r[keyColumn] == k));

I'm assuming that the column identified by keyColumn can be cast to an int.

Answer 3

The approach I've taken in the past is something like:

IEnumerable<int> exceptions = _currentKeys.ToArray().Except(reader.Select(x => x.ID).ToArray());

You have a bit of casting in there that I'm not 100% sure is necessary, but I haven't seen a major performance hit for my needs.

Answer 4

Project the values of the Records collection into a list of Int and use the except function (returns the values in one collection that are not in the other)

var difference = _currentKeys.Except(records.Select(r=>r.ID)