c#LINQ to SQL中出现异常

似乎是通过迭代大型集合来破坏堆栈，但同时将这些对象添加到列表中，从而产生两个大型但基本相同的集合。相反，只需对接受任何IEnumerable的列表使用AddRange。

List<string> codesWithTransactionsInPeriod = new List<string>();
using (var context = new MarketPlaceEntities())
    {
        return codesWithTransactionsInPeriod.AddRange((from transactions in context.Transactions
                               where
                                associatedCodes.Contains(transactions.User.Code) &&
                                transactions.MarketId == marketId &&
                                transactions.Date >= startInclusive &&
                                transactions.Date < endExclusive
                               group transactions by transactions.User.Code into uniqueIds
                               select uniqueIds.Key));
    }

或者不实例化空列表。。。

using (var context = new MarketPlaceEntities())
    {
        return (from transactions in context.Transactions
                               where
                                associatedCodes.Contains(transactions.User.Code) &&
                                transactions.MarketId == marketId &&
                                transactions.Date >= startInclusive &&
                                transactions.Date < endExclusive
                               group transactions by transactions.User.Code into uniqueIds
                               select uniqueIds.Key).ToList<string>();
    }

或者为了保持懒惰。。。（编辑为使用Lazy）

public Lazy<List<string>> LazyCodesWithTransactionsBetweenDates((DateTime startInclusive, DateTime endExclusive, List<string> associatedCodes, int marketId)
{
    return new Lazy<List<string>>(CodesWithTransactionsBetweenDates(startInclusive, endExclusive, associatedCodes, marketId));
}
private List<string> CodesWithTransactionsBetweenDates(DateTime startInclusive, DateTime endExclusive, List<string> associatedCodes, int marketId)
{
    using (var context = new MarketPlaceEntities())
    {
        return (from transactions in context.Transactions
                           where
                            associatedCodes.Contains(transactions.User.Code) &&
                            transactions.MarketId == marketId &&
                            transactions.Date >= startInclusive &&
                            transactions.Date < endExclusive
                           group transactions by transactions.User.Code into uniqueIds
                           select uniqueIds.Key).ToList<string>();
    }
}

这里有两个大问题-对于每个唯一的id键，您要在内存中创建具有单个属性的新对象。此外，您还有一个无用的本地列表，您可以在其中复制所有这些对象。每次列表的容量填满时，都会创建新的内部数组，并将所有对象复制到其中。

您可以使用IEnumerable进行流处理。在这种情况下，您不需要将所有数据保存在内存中：

public IEnumerable<string> CodesWithTransactionsBetweenDates(
         DateTime startInclusive, DateTime endExclusive, 
         List<string> associatedCodes, int marketId)
{
    // do not use local list
    using (var context = new MarketPlaceEntities())
    {
        return from transactions in context.Transactions
                where associatedCodes.Contains(transactions.User.Code) &&
                      transactions.MarketId == marketId &&
                      transactions.Date >= startInclusive &&
                      transactions.Date < endExclusive
                      group transactions by transactions.User.Code into uniqueIds
                      select uniqueIds.Key; // do not create anonymous object
    }
}

如果您需要列表，您可以在此查询中应用ToList()。但您绝对不需要创建匿名对象并将它们复制到本地列表中。

好吧，经过一些尝试和错误，并考虑了一些替代方案，我想出了一个似乎效果很好的解决方案。

public List<string> CodesWithTransactionsBetweenDates(DateTime startInclusive, DateTime endExclusive, List<string> associatedCodes, int marketId)
{
    using (var context = new MarketPlaceEntities())
    {
        var list = (from transactions in context.Transactions                            
                where
                    transactions.MarketId == marketId &&
                    transactions.Date >= startInclusive &&
                    transactions.Date < endExclusive                            
                select transactions.User.Code).Distinct().ToList<string>();
        return list.Where(c => associatedCodes.Contains(c)).ToList();
    }            
}

我想where子句中使用的列表有某种限制，这最终是一个更好的解决方案，因为我限制了用户代码，然后做了一个简单的过滤器，只获取相关代码列表中的代码。