CODEWITHSUNDEEP
c#linq-to-entitiesentity-framework-5
Jed Grant
Jed Grant

Reputation: 1425

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

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.

Upvotes: 1

Views: 4598

Answers (3)

reza okhovat
reza okhovat

Reputation: 134

    public async Task<bool> IsExistBy(List<int> ids, CancellationToken ct)
{
   var temp  = await _repository.TableNoTracking.Where(x=>ids.Contains(x.Id)).ToListAsync(ct);
   return temp.Count == ids.Count;
}

Upvotes: 0

Patryk Ćwiek
Patryk Ćwiek

Reputation: 14328

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.

Upvotes: 7

Nick Butler
Nick Butler

Reputation: 24433

You could try something like this:

// in memory: get list of potential version ids
var potentialIds = item.Versions.Select( o => o.versionID ).ToList();

// hit database ( once ) : get existing version ids
var existingIds = db.ExistingVersions
  .Where( o => potentialIds.Contains( o.versionID ) )
  .Select( o => o.versionID )
  .ToList();

// in memory: filter potential objects
var newVersions = item.Versions
  .Where( o => !existingIds.Contains( o.versionID ) )
  .ToList();

// database inserts:
foreach( var newVersion in newVersions )
{
  ...

One thing to bear in mind is that this is not thread-safe: if something else is adding ExistingVersion rows at the same time, you may try to insert a record that was added after you checked the database.

Upvotes: 3

Related Questions