条款条款不符合条件 [英] LINQ Conditional Where Clauses not working

问题描述

使用：MVC 5，C＃，VS 2013，带有CodeFirst的EF6，SQL Server 2012

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

  IQueryable< vw_Results> qryResults = _db.vw_Results; 
  

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

  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 ==形式[ 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个以上的过滤器，我得到枚举没有得到结果，但第一个过滤器中的数据集确实包含我正在过滤的数据。

  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的查询将是：

 选择col1，col2，col3从dbTable 
其中col1 =New York和col2 =New York，col3 =New York
  

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

感谢大家提供解决方案。

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;

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

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.

解决方案

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

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.

这篇关于条款条款不符合条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！