LINQ 条件 Where 子句不起作用 [英] LINQ Conditional Where Clauses not working

问题描述

使用:MVC 5、C#、VS 2013、EF6 和 CodeFirst、SQL Server 2012

Using: MVC 5, C#, VS 2013, EF6 with CodeFirst, SQL Server 2012

我已经尝试了四种不同的方法来获取数据，没有任何问题.

I have tried the four different ways to get the data without any issues.

IQueryable<vw_Results> qryResults = _db.vw_Results;

我遇到的问题是我有 13 个过滤器选项，所有 13 个的代码都遵循相同的逻辑:

The problem I am encountering is that I have 13 filter options and the code for all 13 follow the same logic:

string fmVal = string.Empty;
if (!string.IsNullOrEmpty(form["Locations"]))
{
    fmVal = form["Locations"].ToString();
    qryResults = qryResults.Where(w => w.LOCATION.CompareTo(fmVal) == 0);
}

if (!string.IsNullOrEmpty(form["ddActionLevels"]))
{
    //qryResults = qryResults.Where(w => w.PAL_ID==form["ddActionLevels"].ToString());
    vbVal = form["ddActionLevels"].ToString(); ;
    //qryResults = qryResults.Where(w => w.AL == vbVal);
    qryResults.Where(w => w.AL.CompareTo(vbVal) >= 0);
}

if (!string.IsNullOrEmpty(form["btnGenericRpt"]))
{
    qryResults.Where(w => w.LOCATION != "BB1");
}

if (!string.IsNullOrEmpty(form["ddProjects"]))
{
    vbVal = form["ddProjects"].ToString();
    qryResults.Where(w => w.PROJECT == vbVal);
}
//...
myModel.Results = qryResults.ToList();
return View(myModel);

如果我只提供 1 个过滤器，我会得到我想要的数据.一旦我提供超过 1 个过滤器，我就会得到枚举未产生任何结果"，但来自第一个过滤器的数据集确实包含我正在过滤的数据.

If I only provide 1 filter, I get the data that I want. As soon as I provide more than 1 filter, I get the "Enumeration yielded no results" yet the data-set from the first filter does contain the data I am filtering on.

推荐答案

哇，我不敢相信问题/解决方案是我的问题.因为我在 .WHERE 子句中使用了相同的变量 (vbVal)，所以在获取数据时，查询使用的是 vbVal 的最后一个值，因此不会返回任何数据.我猜 LINQ 使用 ByRef 作为变量，所以我的查询最终会是:

Wow, I can't believe what the problem/solution was to my issue. Because I was using the same variable (vbVal) in the .WHERE clause, when it was time to get the data, the query was using the last value of vbVal and thus not returning any data back. I'm guessing that LINQ uses ByRef for variables so my query would end up as:

vbVal = form["filter1"]; {"North America"}
qryResults = qryResults.Where (w=>w.col1 == vbVal);
vbVal = form["filter2"]; {"USA"}
qryResults = qryResults.Where (w=>w.col2 == vbVal);
vbVal = form["filter3"]; {"New York"}
qryResults = qryResults.Where (w=>w.col2 == vbVal);

返回 SQL 的查询是:

The query back to SQL would be:

Select col1, col2, col3 From dbTable 
Where col1 = "New York" and col2 = "New York" and col3 = "New York"

将每个过滤器选项分配给一个唯一变量解决了我的问题.

Assigning each filter option to a unique variable solved my problem.

感谢大家提供解决方案.

Thank you all for providing solutions.

这篇关于LINQ 条件 Where 子句不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！