使用IQueryable，foreach和多个Where时的LINQ to SQL错误(或非常奇怪的功能) [英] LINQ to SQL bug (or very strange feature) when using IQueryable, foreach, and multiple Where

问题描述

我遇到了一种情况，其中LINQ to SQL的行为非常奇怪.我想知道我做错了什么.但是我认为这确实是一个错误.

I ran into a scenario where LINQ to SQL acts very strangely. I would like to know if I'm doing something wrong. But I think there is a real possibility that it's a bug.

下面粘贴的代码不是我的真实代码.这是我使用Northwind数据库为此帖子创建的简化版本.

The code pasted below isn't my real code. It is a simplified version I created for this post, using the Northwind database.

背景知识:我有一个方法，它使用IQueryable的Product和一个过滤器对象"(我将在稍后描述).它应该基于过滤器对象"在IQueryable上运行一些"Where"扩展方法，然后返回IQueryable.

A little background: I have a method that takes an IQueryable of Product and a "filter object" (which I will describe in a minute). It should run some "Where" extension methods on the IQueryable, based on the "filter object", and then return the IQueryable.

所谓的过滤器对象"是具有以下结构的匿名类型的System.Collections.Generic.List:{ column = fieldEnum, id = int }

The so-called "filter object" is a System.Collections.Generic.List of an anonymous type of this structure: { column = fieldEnum, id = int }

fieldEnum是Products表中不同列的枚举，我可能希望将其用于过滤.

The fieldEnum is an enum of the different columns of the Products table that I would possibly like to use for the filtering.

只看一下它，而不是进一步解释我的代码是如何工作的.很简单.

Instead of explaining further how my code works, it's easier if you just take a look at it. It's simple to follow.

enum filterType { supplier = 1, category }
public IQueryable<Product> getIQueryableProducts()
{
    NorthwindDataClassesDataContext db = new NorthwindDataClassesDataContext();
    IQueryable<Product> query = db.Products.AsQueryable();

    //this section is just for the example. It creates a Generic List of an Anonymous Type
    //with two objects. In real life I get the same kind of collection, but it isn't hard coded like here
    var filter1 = new { column = filterType.supplier, id = 7 };
    var filter2 = new { column = filterType.category, id = 3 };
    var filterList = (new[] { filter1 }).ToList();
    filterList.Add(filter2);

    foreach(var oFilter in filterList)
    {
        switch (oFilter.column)
        {
            case filterType.supplier:
                query = query.Where(p => p.SupplierID == oFilter.id);
                break;
            case filterType.category:
                query = query.Where(p => p.CategoryID == oFilter.id);
                break;
            default:
                break;
        }
    }
    return query;
}

所以这是一个例子.假设列表包含两个匿名类型的项{ column = fieldEnum.Supplier, id = 7 }和{ column = fieldEnum.Category, id = 3}.

So here is an example. Let's say the List contains two items of this anonymous type, { column = fieldEnum.Supplier, id = 7 } and { column = fieldEnum.Category, id = 3}.

运行上面的代码后，IQueryable对象的基础SQL查询应包含:

After running the code above, the underlying SQL query of the IQueryable object should contain:

WHERE SupplierID = 7 AND CategoryID = 3

但是实际上，在代码运行之后，要执行的SQL是

But in reality, after the code runs the SQL that gets executed is

WHERE SupplierID = 3 AND CategoryID = 3

我尝试将query定义为属性，并在setter上设置断点，以为我可以在不应该更改的地方捕捉到正在更改的内容.但是一切都很好.因此，我只是在每个命令之后检查了基础SQL.我意识到第一个Where运行正常，而query保持正常运行(表示SupplierID = 7)，直到第二次运行foreach循环之后.在oFilter成为第二个匿名类型项(而不是第一个匿名类型项)之后，查询" SQL更改为Supplier = 3.因此，这里面必须发生的是，不仅仅是记住Supplier等于7，LINQ to SQL还记得供应商应该等于oFilter.id.但是oFilter是foreach循环中单个项目的名称，在迭代之后，它的含义有所不同.

I tried defining query as a property and setting a breakpoint on the setter, thinking I could catch what's changing it when it shouldn't be. But everything was supposedly fine. So instead I just checked the underlying SQL after every command. I realized that the first Where runs fine, and query stays fine (meaning SupplierID = 7) until right after the foreach loop runs the second time. Right after oFilter becomes the second anonymous type item, and not the first, the 'query' SQL changes to Supplier = 3. So what must be happening here under-the-hood is that instead of just remembering that Supplier should equal 7, LINQ to SQL remembers that Supplier should equal oFilter.id. But oFilter is a name of a single item of a foreach loop, and it means something different after it iterates.

推荐答案

我只浏览了您的问题，但我90％的确定您应该阅读

I have only glanced at your question, but I am 90% sure that you should read the first section of On lambdas, capture, and mutability (which includes links to 5 similar SO questions) and all will become clear.

其基本要点是示例中的变量oFilter是通过 reference 而不是通过 value 捕获在闭包中的.这意味着一旦循环完成迭代，变量的引用即指向最后一个变量，因此在lambda执行时评估的值也将是最后一个变量.

The basic gist of it is that the variable oFilter in your example has been captured in the closure by reference and not by value. That means that once the loop finishes iterating, the variable's reference is to the last one, so the value as evaluated at lambda execution time is the final one as well.

解决方法是在foreach循环内插入一个新变量，其作用域仅是该迭代，而不是整个循环:

The cure is to insert a new variable inside the foreach loop whose scope is only that iteration rather than the whole loop:

foreach(var oFilter in filterList)
{
    var filter = oFilter; // add this
    switch (oFilter.column) // this doesn't have to change, but can for consistency
    {
        case filterType.supplier:
            query = query.Where(p => p.SupplierID == filter.id); // use `filter` here
            break;

现在，每个闭包都位于一个不同 filter变量上，该变量在每个循环内被重新声明，您的代码将按预期运行.

Now each closure is over a different filter variable that is declared anew inside of each loop, and your code will run as expected.

这篇关于使用IQueryable，foreach和多个Where时的LINQ to SQL错误(或非常奇怪的功能)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！