Filtering IQueryable is bringing back wrong results

Question

I have method which accepts IQueryable<T> as a parameter called attachments. Inside this method I filter the query further in multiple if statements. So my code is as follows:

if(firstCondition)
{
   attachments = attachments.Where(i => i.TestItemId == 1); //3 records in db
   DoWork(attachments);
}
if(secondCondition)
{
   attachments = attachments.Where(i => i.TestItemId == 2); //2 records in db
   DoWork(attachments);
}
...

Inside DoWork();I do:

foreach(var items in attachments)
{
   //write attachment name to file here
}

In the database I have a total of 5 records which inside the first if statement I get the appropriate results back. However in the second if condition I get 0 result back in the query. Can someone tell me where I am going wrong please.

Please note both of the if conditions will be true.

Concatenating `Where` conditions results in an `AND` query. So effectively the two combined result in `SELECT * FROM attachments WHERE TestItemId = 1 AND TestItemId = 2`. Perhaps you were thinking of an `OR` — Silvermind, May 21 '19 at 08:50
@Silvermind What would you suggest the approach I take? Will I have a create a `var` in every if condition — Izzy, May 21 '19 at 08:52
Are you wanting to have OR logic on your conditions? ie Id is 1 or 2? — Chris, May 21 '19 at 08:53
Possible duplicate of [Extend IQueryable Where() as OR instead of AND relationship](https://stackoverflow.com/questions/930677/extend-iqueryablet-where-as-or-instead-of-and-relationship) — Silvermind, May 21 '19 at 08:53
@Chris Inside first if I only want items with Id 1 and inside second if with Id 2 and so not. I have a total of 8 conditions to meet — Izzy, May 21 '19 at 08:54
@Code: So they are completely independent things? In that case dont' reassign `attachments`. Better still put each of your conditions in their own method and pass `attachment` in to them and then you will be hard pressed to repeat this behaviour. Your problem seems to be that by the second condition you are working on the already reduced set of items in attachment because of the assignment. The other option is putting your where clause inline with the DoWork call. — Chris, May 21 '19 at 08:57
@Chris Yes they're complete independent things. Can you provide an example please — Izzy, May 21 '19 at 09:00

score 4 · Answer 1 · answered May 21 '19 at 08:53

4

You shoudn't assign attachments for the firstCondition, result will be filter by 2 conditions: TestItemId == 1 && TestItemId == 2. => It always return empty list;

answered May 21 '19 at 08:53

Tan Sang

1,897
1
16
28

score 3 · Answer 2 · answered May 21 '19 at 08:53

3

Concatenation of conditions

The problem is with the assignation, causing a concatenation of the Where clausules.

attachments = attachments.Where(i => i.TestItemId == 1);
attachments = attachments.Where(i => i.TestItemId == 2);

The following code is the same as the above:

attachments.Where(i => i.TestItemId == 1).Where(i => i.TestItemId == 2);

If you remove the attachments = from both ifs you won't have any problem.

if(firstCondition)
{
   DoWork(attachments.Where(i => i.TestItemId == 1));
}

if(secondCondition)
{
   DoWork(attachments.Where(i => i.TestItemId == 2));
}

answered May 21 '19 at 08:53

Oscar Vicente Perez

847
7
23

The downside of your last block of code is it is now doing multiple SQL queries. – DavidG May 21 '19 at 09:00
Before it was doing multiple SQLs. You need to use ToList before to avoid that. But I don't know what kind of Object is `attachments`, it can be a List converted into an IQueryable. – Oscar Vicente Perez May 21 '19 at 10:04
I know, I'm just saying that the entire thing could have been done with a single call. – DavidG May 21 '19 at 10:05
`attachment`is `IQueryable` – Izzy May 22 '19 at 08:35

Filtering IQueryable is bringing back wrong results

2 Answers2

Concatenation of conditions