Clever Geek Handbook

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

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 the name of the given column, 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 values ​​[1, 4, 5] in the column named ID. If I call Update, I need to get a collection containing [2, 3].

As always, I feel like it's super simple, but the fact that the two collections are different completely threw me off. Combined with the fact that I haven't used LINQ yet.

+2


source to share


4 answers


The trick is to query for a list of primary keys for keys that your reader does not contain. The where clause specifies which column in the reader to compare. However, they must be of the same data type.

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 are not of the same data type, simply convert them to a memory collection to match the DB collection type. IE, if your primary key is stored as varchar in the database, you will hide the _currentKeys like this:

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


source


You can do it:

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


I am guessing the column identified by keyColumn can be cast to int.

0


source


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

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

You have a few castings that I'm not 100% sure about, but I haven't seen much performance success for my needs.

0


source


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

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


source






More articles:


All Articles