LINQ Conditional Where Clauses not working

c# entity-framework entity-framework-6 linq

Question

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.

1
0
8/5/2015 11:14:24 PM

Accepted Answer

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.

1
8/5/2015 7:09:55 PM

Popular Answer

The main problem I see with your code is the lines like this:

qryResults.Where(w => w.AL.CompareTo(vbVal) >= 0);

You start with qryResults, compute a .Where(...) but you don't re-assign the query back to qryResults.

Try this:

qryResults = qryResults.Where(w => w.AL.CompareTo(vbVal) >= 0);

Although this doesn't explain why you're getting no results back. That'll be a question you should tackle when you get the code right.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow