CODEWITHSUNDEEP
c#entity-frameworklinq
Harald Coppoolse
Harald Coppoolse

Reputation: 30454

Entity Framework / Linq: Efficiently detect if all elements exist in database

Addition after comment, my question was not clear enough

Some software accessed a database and retrieved a collection of items. He selected a subset of them and called me to ask to process the collection. Between the other software's access to the database and its call to me items might be removed by others.

Other Process:

IEnumerable<MyType> itemsToProcess = FetchItemsToProcess();
Process(itemsToProcess);

My procedure:

void Process(IEnumerable<MyType> items)
{
   ...
}

The collection of items is fairly small, and normally all items are expected to be present in the database, with unchanged primary and foreign keys, However I can't be sure whether all items still exist.

Continuing original question

It is not a problem if during processing I detect that an item does not exist in the database anymore, it is only a waste of time. Therefore before processing I want to check if all items exist in the database.

I have checked that all items in my sequence have a non-zero ID. Now I need to check if all IDs are IDs of existing items in the database:

Not efficient solution: get the Ids of all existing items into local memory and use Except

IEnumerable<MyType> itemsToProcess = ...
IEnumerable<int> idsToProcess = itemsToProcess.Select(item => item.Id);
IEnumerable<int> existingIds = myDbContext.MyItems.Select(item => item.Id);

bool nonExistingItemsDetected = idsToProcess.Except(existingIds).Any();

The following method requires one DB accesses per item to check:

IEnumerable<MyType> itemsToProcess = ...
IEnumerable<int> idsToProcess = itemsToProcess.Select(item => item.Id);
bool nonExistingItemsDetected = idsToProcess
    .Where(idToProcess => !myDbContext.MyItems.Any(item => item.Id == idToProcess);

Because the sequence of items to process is fairly small I'd like to use the database to detect if any ID does not exist. I think I need to make an IQueryable with a Contains

bool nonExistinItemsDetected = myDbContext.MyItems
  .Any( ?? )

What predicate can I use to detect if idsToProcess contains ids of items that do not exist.

Upvotes: 0

Views: 1963

Answers (1)

Oliver
Oliver

Reputation: 9002

If you need to process the items anyway, you'll need to get them from the database. In which case, you might as well use something like the following to obtain the matching items:

var existingDbItems = dbContext.MyItems.Where(i => localIdCollection.Contains(i.Id));

Then you can simply check that the count of distinct id's matches the number of records returned before proceeding.

Ensure the collection of id's that you use .Contains() on is an in-memory collection (not a store expression) otherwise you may see an exception.

EDIT

Based on your comment, you could change the query above to:

var existingDbItemsCount = dbContext.MyItems
     .Where(i => localIdCollection.Contains(i.Id)).Distinct().Count();

So that you're only obtaining the count, rather than all the records.

If the items are so volatile that you have to check they exist at this point, unless you're working within a transaction, there still may be problems by the time your code modifies the database.

Upvotes: 1

Related Questions