LinqToSQL将本地列表加入表-混淆 [英] LinqToSQL joining local list to table - confusion

问题描述

我正在使用LinqToSQL开发应用程序.在此过程中，我创建了一个整数列表，这些整数表示要过滤的键.过去，每次执行此操作并尝试将列表和数据表连接在一起时，都会出现以下错误:

I am developing an application using LinqToSQL. As part of this I create a list of integers, which represent keys I want to filter. Every time in the past that I've done this and tried to join my list and the data table I get the following error:

除Contains()运算符外，不能在LINQ to SQL实现中使用本地序列

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator

现在这很好，因为据我了解，这是LinqToSQl的极限/功能.如图所示，我一直在使用Contains运算符进行查询:

Now this is fine because, as I understand it, it is a limitaiton/feature of LinqToSQl. I've been using the Contains operator for my queries as shown:

List<CargoProduct> cargoProducts = context.CargoProducts
                                   .Where(cp => cargos.Contains(cp.CargoID))
                                    .ToList();

最近，我在Contains中遇到了2100件物品的限制，因此一直在寻找其他方法来做到这一点，最终提出以下建议:

Recently I've come across the 2100 item limitation in Contains, so was looking for other ways to do it, eventually coming up with the following:

List<CargoProduct> cargoProducts = context.CargoProducts.AsEnumerable()
                                   .Join(cargos, cp => cp.CargoID, c => c, (cp, c) => cp)
                                   .ToList();

现在，这很好，所以我为其他开发者整理了一封知识共享电子邮件，以防他们遇到此限制.我正在尝试获取错误消息，因此将另一个查询放在一起，而不是我期望的失败:

Now, that works fine so I was putting together a knowledge sharing email for the other developers in case they came across this limitation. I was trying to get the error message so put together another query than I'd expect to fail:

List<CargoProduct> results = (from c in cargos
                              join cp in context.CargoProducts on c equals cp.CargoID
                              select cp).ToList();

令我惊讶的是，这不仅没有引发错误，而且返回的结果与上一个查询完全相同.那么，我在这里想念的是什么?我敢肯定这很明显！

Much to my surprise, not only did this not throw an error but it returned exactly the same results as the previous query. So, what am I missing here? I'm sure it's something obvious!

我的LinqToSQl连接是供参考的，并且将货物实例化为:

For reference context is my LinqToSQl connection and cargos is instantiated as:

List<int> cargos = context.Cargos.Select(c => c.CargoID).ToList();

更新

正如回复中所提到的，确实似乎是我加入东西的顺序，就像我使用以下命令一样，我得到了预期的错误消息:

Update

As mentioned in the reply it would indeed appear to be the order in which I am joining stuff, as if I use the following then I get the expected error message:

List<CargoProduct> test3 = (from cp in context.CargoProducts
             join c in cargos on cp.CargoID equals c
             select cp).ToList();

这是有趣的功能，我想我理解它为什么做它所做的事情.可能是一个不错的解决方法，而不是对较小的数据集使用包含".

It's interesting functionality and I think I understand why it is doing what it does. Could be a good workaround instead of using Contains for smaller datasets.

推荐答案

在此查询中

List<CargoProduct> results = (from c in cargos
                         join cp in context.CargoProducts on c equals cp.CargoID
                         select cp).ToList();

join语句中的左操作数的类型为IEnumerable，然后根据方法重载解析选择Enumerable.Join扩展方法.这意味着整个CargoProducts表都将加载到内存中，并通过Linq To Objects进行过滤.类似于context.CargoProducts.AsEnumerable().

the left operand in the join statement is of type IEnumerable, then the Enumerable.Join extension method is being chosen on method overload resolution. This means that the whole CargoProducts table is being loaded in memory and and filtered via Linq To Objects. It is similar to do context.CargoProducts.AsEnumerable().

这篇关于LinqToSQL将本地列表加入表-混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！