Check if list of entities already exist in Database using linq to entities

Question

I have a list of entities coming from an external source. I need to compare it to what I already have, and only add the ones that don't exist. Pseudo code below.

 var newVersions = item.Versions
      .Where(s => db.ExistingVersions
           .Select(t=>t.versionID)
           .DoesNotContains(s.versionID));

That obviously doesn't work, and I'm not sure how to fix it. I don't want to use a for loop because I believe that would mean I would have hundreds of database hits just to check the versions on each item. I am loading multiple items, each item has as many as 100 versions.

Answer 1

If there's nothing more to the question than I think, then it shouldn't be complicated.

Assuming that VersionID is unique identifier, then you can do this:

var existingVersions = db.ExistingVersions.Select(x => x.VersionID).ToList();

mind you, for Contains it would be better to:

var existingVersions = new HashSet(db.ExistingVersions.Select(x => x.VersionID).ToList());

[EDIT]: Per Magnus's comment, you can drop the ToList from above code snippet.

and then:

var newVersions = items.Versions.Where(x => !existingVersions.Contains(x.VersionID));

This is probably the most performant, 'cause when calling the database, you select only the VersionID. Other option involves writing a custom IEqualityComparer<T> and using Except, but you'd have to pull everything from the DB which may be more costly.