IQueryable WHERE inside a for loop does not produce expected result

Question

IQueryable WHERE inside a for loop does not produce expected result

.net-core c# entity-framework-core linq mysql

Question

Objective:

Implementing AND filter. Where users input list of keywords in an input, then the list gets filtered based on the first word, then the next and so on. For instance, when they input "May", their list should be filtered to show only records for the month of may. In case, they input "May June", they should get empty list. Because, you shouldn't find records for the month of June within a list for the month of May

My failed attempt:

Below example does not work as I expected to

List<string> keywords; // e.g. "May", "June"

for (int i = 0; i < keywords.Length; i++) {                
    string searchTerm = keywords[i];
    entityObj = entityObj.Where(x =>
            x.Month.ToLower().Contains(searchTerm) ||
            x.Year.ToString().ToLower().Contains(searchTerm) ||
            x.Product.ToLower().Contains(searchTerm));
}

entityObj is of type IQueryable<SomeEntity>. Assume there is a table in the database with Month, Year and Product columns. On the first iteration, searchTerm will have May as its value. So, entityObj should return IQueryable object for records that only has May in the Month column. I expect the second iteration to return nothing, since we are looking for value of June in a result of the previous iteration which has only May result. However, I am getting records that have June value instead. No matter how many keywords I have, it is always returning the result based on the last element in the list which is the last iteration.

When I do below

string searchTerm = "May";
entityObj = entityObj.Where(x =>
            x.Month.ToLower().Contains(searchTerm) ||
            x.Year.ToString().ToLower().Contains(searchTerm) ||
            x.Product.ToLower().Contains(searchTerm));

searchTerm = "June";
entityObj = entityObj.Where(x =>
            x.Month.ToLower().Contains(searchTerm) ||
            x.Year.ToString().ToLower().Contains(searchTerm) ||
            x.Product.ToLower().Contains(searchTerm));

I get the result that I expect, empty list. However, When I do that inside a for loop, it does not work. Am I missing something here?

Steve Mclean

View more on Stack Overflow

RAHUL S R · Accepted Answer · 10/22/2019 9:50:32 AM

the issue must be because of using same variable entityObj

first of all entityObj is IQueriable right ? that means it just a query and not a collection or list

secondly do not use same variable to store the result as well

instead try this it should work fine

string searchTerm = "May";
var mainList= entityObj.Where(x =>
            x.Month.ToLower().Contains(searchTerm) ||
            x.Year.ToString().ToLower().Contains(searchTerm) ||
            x.Product.ToLower().Contains(searchTerm)).ToList();

searchTerm = "June";
var subList= mainList.Where(x =>
            x.Month.ToLower().Contains(searchTerm) ||
            x.Year.ToString().ToLower().Contains(searchTerm) ||
            x.Product.ToLower().Contains(searchTerm));

i am sure this should work but even this one dosent work then you might want to look into how c# is referencing variable assigning memory allocation

dont go too deep but there is shellowCoy and deepcopy techniques ..you might wanna look into that

https://www.geeksforgeeks.org/shallow-copy-and-deep-copy-in-c-sharp/

IQueryable WHERE inside a for loop does not produce expected result

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related