Linq filtering results with multiple Where clauses
I am trying to use EF 5 to apply multiple search criteria to a set of results (in this case to search the library directory). Here's the relevant code:
public IQueryable<LibraryResource> GetSearchResults(string SearchCriteria, int? limit = null)
{
List<string> criteria = SearchCriteria.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToList();
IQueryable<LibraryResource> allResults = context.LibraryResources.Include("Type").Where(r => r.AuditInfo.DeletedAt == null);
foreach (string criterion in criteria)
{
allResults = allResults.Where(r => (r.Title.Contains(criterion) || r.Keywords.Contains(criterion) || r.Author.Contains(criterion) || r.Comments.Contains(criterion)));
}
allResults = allResults.OrderBy(r => r.Title);
if (limit.HasValue) allResults = allResults.Take(limit.Value);
return allResults;
}
Search Example Criteria = "epoch history"
For some reason, only the last criterion applies. For example, in the example above, all books with "era" in title, author, keywords, and comments are returned without filtering by "history". I went through the code and the loop runs twice, each time with the appropriate criteria. Do you see something that I can't? Thank!
source to share
You are the victim of changing the value of a private variable.
Change your code:
foreach (string criterion in criteria)
{
var crit = criterion;
allResults = allResults.Where(/* use crit here, not criterion */);
}
The problem is that while you are creating the query, your filter expressions are closed over the variable criterion
, effectively pulling it in scope at the point where the query is made. However, there criterion
will only be one value at this time (the last of which was completed), so all but the last of your filters will actually be made duplicates of the last.
Making a local copy criterion
and referencing what's inside the expressions fixes the problem because it crit
's a different local variable each time with a lifetime that doesn't propagate from one loop iteration to the next.
For more details, you can read Is there a reason to reuse C # variable in foreach? where it also mentions that C # 5.0 will accept which applies to this scenario: the lifetime of the loop variable criterion
will change, making this code correct without additional local.
source to share