Except() on EntityCollection vs List

Question

Using EntityFramework 4. I have an EntityCollection<SomeEntity> currentEntities with ~500k entities and a List<SomeEntity> importedEntities also with ~500k records. I want to have the list of all records occuring in currentEntities which don't exist in importedEntities.

Calling currentEntities.Select(x => x.ID).Except(importedEntities.Select(x => x.ID)) to get unique IDs of occurring records causes a System.OutOfMemoryException because it apparently loads all of the entities into the memory.

Calling currentEntities.Where(x => !importedEntities.Any(y => y.ID == x.ID)) fails with NotSupportedException ("Only primitive types ('such as Int32, String, and Guid') are supported in this context").

currentEntities is on the SQL Server 2008 R2 database, while importedEntities are in memory.

Is this even possible in L2E ?

Answer 1

Doing an except on two "large" lists of integers in memory is not a point. If I do Enumerable.Range(0, 500000).Except(Enumerable.Range(500, 500000)).Count() it returns 500 before I can say OutOfMemoryException. So I think reducing your problem to an except of integers should work for you:

var newIds = importedEntities.Select(x => x.ID).ToArray()
             .Except(currentEntities.Select(x => x.ID).ToArray()).ToArray();

Thus, only integers will be loaded into memory, no entity objects.

Now you can do:

importedEntities.Where(x => newIds.Contains(x.ID))

provided that newIds is not too long. What is too long? The linq statement produces an IN clause, which can easily contain a couple of thousands of items, but if it is longer than, say, 10,000 you probably should process the IDs in chunks.

By the way (1). I'm assuming here that both lists are in different contexts, maybe even different databases. However, if they are in the same context, you might have success with:

importedEntities.Where(x => !currentEntities.Select(y => y.ID)
                           .Any(id => id == x.ID))

This produces a NOT EXISTS sql query. You may still run into an OutOfMemoryException though if there are "many" new items. If so, you could use a paging mechanism (Skip - Take) to process the new items in chunks.

By the way (2), I swapped currentEntities and importedEntities, because I assume you are interested in the new imported items, please undo that if I'm wrong.

Answer 2

Try this.

The following is for when importedEntities is an in-memory collection, not a collection in the database.

var importedEntityIds = importedEntities
    .Select(x => x.ID).ToList(); // Convert to a list of some value type.
var result = currentEntities
    .Where(x => !importedEntityIds.Contains(x.ID))
    .ToList();

The following is for when importedEntities is a collection in the database.

var importedEntityIds = importedEntities
    .Select(x => x.ID); // This will be translated in SQL to a NOT IN (...)
var result = currentEntities
    .Where(x => !importedEntityIds.Contains(x.ID))
    .ToList();

Answer 3

I am assuming you are trying to figure out what new entries you need to add to some system or something. This is not an uncommon occurrence. Because your dataset is so big, either both cant exist in memory at the same time OR the various LINQ extension methods create copies of the collections in memory which busts through the memory max because you now have 3 or 4 copies of the lists in memory.

You might be able to use the fact that the results will be in an IEnumerable which will only load chunks of records as it iterates over the results from sql server.

Try this

foreach (var current in currentEntities)
{
    if (importedEntities.Contains(current))
    {
        importedEntities.Remove(current);
    }
}

The code assumes that the Equals and GetHashCode methods are overriden to compare the objects by the properties and not the default ones from object base class which just compares pointers or some low level stuff.

note: if you were to do the following

foreach (var current in currentEntities.ToList())
{
    if (importedEntities.Contains(current))
    {
        importedEntities.Remove(current);
    }
}

then you will get the System.OutOfMemoryException because now the whole database table is in memory not just the row you are trying to compare. So DONT do the ToList or something like that.

Answer 4

Select all ids from the imported entities:

var importedids = importedEntities.Select(entity => entity.ID).ToList();

Create a HashSet:

var importedidshashset = new HashSet<int>(importedids);

Filter currentEntities:

currentEntities.Where(entity => !importedidshashset.Contains(entity.ID)).ToList().