将巨大列表与Entity Framework中的数据库表记录进行比较 [英] Compare an huge list with a database table records in Entity Framework

问题描述

我有很多字符串，我想与之比较数据库表的记录。最佳解决方案是什么？

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

您可以假设指定表的名称为 ATable ，其结构如下：

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;}
}

我写了以下代码

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

我检查了生成的日志，发现上面的代码已转换为SQL IN（...）语句，并且由于列表应用程序崩溃的原因很大。

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.

谢谢

推荐答案

因为，EF包含一项改进，可加快Enumerable.Contains的翻译。

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

如果您使用的是EF的早期版本，或者列表的大小太大，请使用@ Dan-o提议，将较大的列表分成较小的块。为此，您可以使用@divega在此发布。下面，我将该解决方案调整为适合您的问题上下文：

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();

这篇关于将巨大列表与Entity Framework中的数据库表记录进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！