How to chain alternative conditions in where clause in Entity Framework

Question

I'm trying to rewrite the following query as a separate statements:

var sql = Repository.Products.AsQueryable();

sql = sql.Where(x => x.Name == "aaaaa" || x.Name == "bbbbb");

If I do it like this:

sql = sql.Where(x => x.Name == "aaaaa");
sql = sql.Where(x => x.Name == "bbbbb");

then the resulting query is equal to:

sql = sql.Where(x => x.Name == "aaaaa" && x.Name == "bbbbb");

Is there any ideas how to do it the right way?

Take a look at http://stackoverflow.com/questions/2101540/linq-or-equivalent-of-where and see if it relates to what you want to achieve, since there must be a good reason for wanting to split up the condition. — Pricey, Jun 30 '13 at 22:31
I use this in a search filter, which makes it possible for a user to supply variable number of conditions to include in a query. So the number of conditions is unknown in the beginning — dodbrian, Jun 30 '13 at 22:37
See @GuruStron answer. `Where` predicate only combines in an `And` manner. If you want an `Or` you have to do fancy Expression Tree unwrapping. Use `PredicateBuilder` — Simon Belanger, Jun 30 '13 at 22:39

It'sNotALie. · Accepted Answer · 2013-06-30T23:14:21.810

13

The right way... is that. I mean, you could write it as

sql.Where(x => x.Name == "aaaaa").Concat(sql.Where(x => x.Name == "bbbbb"));

but that's slower, unordered and looks weirder too. I don't know what you're looking for, because the way that you posted is the right way to do it.

However, it seems you want to build the expression dynamically (judging from your comment). If so, then you're looking for a PredicateBuilder:

var predicate = PredicateBuilder.False<YourType>();
var search = new[] {"aaaaa", "bbbbb"};
foreach (string y in search)
{
    string name = y;
    predicate = predicate.Or(x => x.Name == name);
}
sql = sql.Where(predicate);

The code for PredicateBuilder is here.

edited Jun 30 '13 at 23:14

answered Jun 30 '13 at 22:32

It'sNotALie.

22,289
12
68
103

Thanks, it's exactly what I was looking for. The generated SQL looks like this: `SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[Name] IN (N'aaaaa',N'bbbbb')` – dodbrian Jun 30 '13 at 23:13
@user2537328: You can have this special SQL more direct without PredicateBuilder: `sql = sql.Where(x => search.Contains(x.Name))`. PredicateBuilder is useful if you have an OR between conditions on *different* properties. – Slauma Jun 30 '13 at 23:23
@Slauma This is (I'm guessing) an example. – It'sNotALie. Jun 30 '13 at 23:25
Yes, I think so. I was only refering to the `IN` clause in the comment above. `PredicateBuilder` solves more general conditions of course. – Slauma Jun 30 '13 at 23:33
@Slauma: Interesting point to keep in mind, but in my case I do use different properties (sample in the question is very simplified), so I think using predicates is the best decision so far. – dodbrian Jun 30 '13 at 23:36
Old topic, but if you are using EF Core this will fail by default - and legacy / .NET framework EF will execute this on the client rather than on the server, which is why it is disabled by default in EF Core. – majorpayne27 Nov 29 '22 at 17:15

score 4 · Answer 2 · answered Jun 30 '13 at 22:37

4

if you want to build you predicate based on come conditions use Predicate Builder

answered Jun 30 '13 at 22:37

Guru Stron

102,774
10
95
132

How to chain alternative conditions in where clause in Entity Framework

2 Answers2

Linked