4

If I want to append a AND statement to my query, I can do:

query = query.Where(obj=>obj.Id == id);

if(name.HasValue)
  query = query.Where(obj=>obj.Name == name);

and it will give me:

query.Where(obj=>obj.Id == id && obj.Name == name)

How can I append a OR statement that will result in:

query.Where(obj=>obj.Id == id || obj.Name == name)
James
  • 2,811
  • 3
  • 25
  • 29
  • I don't think you can do this. Your first .Where refines your result set to include only items that meet the criteria. A second .Where further refines the result set thus it can further eliminate but not add to. – Rick Petersen Nov 19 '12 at 21:04
  • `AND` *filters*, it never needs more data than it already has. `OR` *merges*, it would have to re-run your original query against the new predicate. – Frédéric Hamidi Nov 19 '12 at 21:05
  • You would have to `Select` first. The `where` cannot be concatenated either, at least not in LinqToEntities. Like this it would only use the last `where` – Silvermind Nov 19 '12 at 21:36

5 Answers5

6

You can't do it natively. However, you can use PredicateBuilder to compose the query before you run it, and it supports ORs.

var predicate = PredicateBuilder.False<Product>();
predicate = predicate.Or (obj=>obj.Id == id);
if(name.HasValue)  predicate = predicate.Or (obj=>obj.Name == name);

return query.Where(predicate);
Bobson
  • 13,498
  • 5
  • 55
  • 80
3

Simply this if I'm not missing something:

query.Where(obj=>obj.Id == id || (obj.Name == name && name.HasValue))

You might want to read this question (my question...) and answer for more complicated scenarios:
How to filter IEnumerable based on an entity input parameter

Community
  • 1
  • 1
gdoron
  • 147,333
  • 58
  • 291
  • 367
0

I would just build this into a single condition:

if (name.HasValue)
    query = query.Where(obj=>obj.Id == id && obj.Name == name);
else
    query = query.Where(obj=>obj.Id == id);
Reed Copsey
  • 554,122
  • 78
  • 1,158
  • 1,373
  • Why not `query.Where(obj=>obj.Id == id || (obj.Name == name && name.HasValue))` as in my answer? – gdoron Nov 19 '12 at 21:05
  • @gdoron I don't believe all providers (ie: LINQ to SQL) will handle the nullable check correctly when mapping the expression tree out. – Reed Copsey Nov 19 '12 at 21:06
  • I never had troubles with the providers I used (NH & EF) – gdoron Nov 19 '12 at 21:07
  • @gdoron Okay - LINQ to SQL is particularly picky about stuff like this - NH & EF are much better, though with EF it depends on the back end provider, too. – Reed Copsey Nov 19 '12 at 21:08
0

I would use gdoron's solution, but if it seems unpractical for larger sets of queries, a slightly more complicated solution containing set operations might help you:

var queryById = query.Where(obj => obj.Id == id);
var queryByName = query.Where(obj => obj.Name == name);
query = queryById.Union(queryByName);

It gets much more difficult if your original query contains duplicate items.

Another way may be using Expression to formulate your queries. You can modify the expression tree before executing it, so you can add more conditions to the Where sub-tree. That is a whole lot of work and it's an overkill on 99.9% (rough estimate :) ) of cases.

Honza Brestan
  • 10,637
  • 2
  • 32
  • 43
0

Part of the problem is that you over write your original query. Your OR operation would be equivalent to:

subquery1 = query.Where(obj=>obj.Id == id);
subquery2 = query.Where(obj=>obj.Name == name);
query = subquery1.Concat(subquery2).Distinct();

As you can see, that becomes pretty unwieldy as well, however if You are going to do this form, then you must maintain the original sequence so that you can get both the right and left sides of the equation processed then you need to be sure duplicates get removed.

Instead of all that, I would try to figure a way to build up that conditional statement dynamically using lambdas e.g.

I haven't actually run this, but something like this should work.

var cond = obj=>obj.Id == id;

...

// need an or
cond = obj=>cond(obj) || obj.Name == name;

query = query.Where(obj=>cond(obj));

Hope this gives you an idea.

Joe Greer
  • 19
  • 1