Compare an huge list with a database table records in Entity Framework

Question

I have a huge list of strings and I want to compare a database table's records with it. What is the best solution?

You can suppose specified table's name is ATable and its structure is like:

public class ATable
{
    [Key]
    public long Id{get;set;}
    [Key]
    public long Name{get;set;}
}

I wrote the following code

using(var context = new MyDbContext())
{
    context.Log = (log) => {Console.WriteLine(log)};
    var result = context.ATables.Where(item => hugeList.Contains(item.Name)).ToList();
}

I checked generated logs and I saw that above code translated to SQL IN(...) statement and because of hugeness of list application crash.

I'm sure there is a good way to solve this problem, then you professionals can show me right one.

Thanks

You can use `context.ATables.ToList()` to save it in memory as object. And then check the condition between two .net objects. — Farhad Jabiyev, Jan 01 '15 at 12:50
You are right, but if my table's record be huge too then what? — Saeed Afshari, Jan 01 '15 at 12:51
In my opinion, you can create a new table. And then insert this list items to this table. And then join these two tables with the help of Linq-To-Entity. And after getting the result, you can truncate the first table. — Farhad Jabiyev, Jan 01 '15 at 12:53
It is not bad idea but I like to gather more recommendations. thanks — Saeed Afshari, Jan 01 '15 at 12:59
You might consider breaking the `hugeList` into smaller chunks.. say, 20 values at a time. — Sam Axe, Jan 01 '15 at 14:59

score 2 · Accepted Answer · edited May 23 '17 at 11:57

Since EF 6 Alpha 1, EF includes an improvement that accelerates the translation of Enumerable.Contains.

If you are using an earlier version of EF or the size of your list is too big, as propose @Dan-o you can break the huge list in smaller chunks. To do this you can use the solution that @divega wrote in this post. Below I adapt that solution to your problem context:

public partial class MyDbContext
{
    public IEnumerable<ATable> GetElements(IEnumerable<long> hugeList, int chunkSize = 100)
    {
        foreach (var chunk in hugeList.Chunk(chunkSize))
        {
            var q = ATables.Where(a => chunk.Contains(a.Name));
            foreach (var item in q)
            {
                yield return item;
            }
        }
    }
}

Extension methods for slicing enumerable sequences:

public static class EnumerableSlicing
{

    private class Status
    {
        public bool EndOfSequence;
    }

    private static IEnumerable<T> TakeOnEnumerator<T>(IEnumerator<T> enumerator, int count,
        Status status)
    {
        while (--count > 0 && (enumerator.MoveNext() || !(status.EndOfSequence = true)))
        {
            yield return enumerator.Current;
        }
    }

    public static IEnumerable<IEnumerable<T>> Chunk<T>(this IEnumerable<T> items, int chunkSize)
    {
        if (chunkSize < 1)
        {
            throw new ArgumentException("Chunks should not be smaller than 1 element");
        }
        var status = new Status { EndOfSequence = false };
        using (var enumerator = items.GetEnumerator())
        {
            while (!status.EndOfSequence)
            {
                yield return TakeOnEnumerator(enumerator, chunkSize, status);
            }
        }
    }
}

Then you can do something like this:

var result= context.GetElements(hugelist).ToList();

Compare an huge list with a database table records in Entity Framework

1 Answers1

Linked