Multiple OR statements with LINQ

Question

I'm trying to create a LINQ Statement for the following MYSQL Query:

SELECT * FROM Movies
WHERE
   movies.Actorid = 1 
AND
(
   (Movies.Duration >= x1 AND Movies.Duration <= x2) OR
   (Movies.Duration >= y1 AND Movies.Duration <= y2) OR
   (Movies.Duration >= z1 AND Movies.Duration <= z2)
)

whereas the number of variables (x,y,z) is unknown. Actually they are stored in a Dictionary<int, int> and I'm now trying to create this kind of WHERE condition with a loop, but somehow I couldn't achieve it.

My code looks as follows:

//Initialize query
IQueryable<Movie> query = context.Movies;

//Add actor query
query = query.Where(x => x.Actorid = 1);

//Add duration queries
foreach(KeyValuePair<int, int> movieduration in moviedurations){
    query.Where(x => x.Duration >= movieduration.Key && x.Duration <= movieduration.Value);
}

//Execute query
List<Movie> result = query.ToList();

Those conditions are getting connected with a AND condition, but I need an OR condition. So I searched for another solution and found a solution with a custom predict function but somehow, I could't make it work. My try looks like the following:

//Initialize query
IQueryable<Movie> query = context.Movies;

//Add actor query
query = query.Where(x => x.Actorid = 1);

//Define variables
Func<Movie, bool> predicate = movie => false;

//Create predict
foreach(KeyValuePair<int, int> movieduration in moviedurations){
    var predicateCopy = predicate;
    predicate = x => predicateCopy(x) || (x.Duration >= movieduration.Key && x.Duration <= movieduration.Value);
 }

//Add predict to where query
query = query.Where(predicate);

//Execute query
List<Movie> result = query.ToList();

I get the error message:

The type "System.Collections.Generic.IEnumerable<Movie>" cannot be converted into "System.Linq.IQueryable<Movie>".

Aside from anything else, you're not actually doing anything with the result of `query.Where` in the loop... — Jon Skeet, Feb 21 '23 at 10:16
Sorry, I don't understand what you mean. I execute the query at the end with a ToList() statement (edited the listing). But I'm not doing anything else with the result of the query. — M4SX5, Feb 21 '23 at 10:31
In the loop, you call `query.Where` but don't use the new query returned by it. Calling `query.Where` doesn't *modify* the current query at all - it returns a *new* query with the given predicate. — Jon Skeet, Feb 21 '23 at 10:37
LINQ works with `Expression`, not `Func` but even if you change predicate to `Expression>` you won't be able to compose predicates. EF doesn't execute the LINQ query, it transaltes it to SQL and needs to inspect and understand all expressions. You can compose AND conditions by chaining `.Where()` calls, eg `query=query.Where(...)` but that doesn't work with `OR`. You need something like [LinqKit's PredicateBuilder](https://github.com/scottksmith95/LINQKit#using-predicatebuilder) to combine multiple expressions in a way that can be translated to SQL — Panagiotis Kanavos, Feb 21 '23 at 10:39
Check [FilterByItems](https://stackoverflow.com/questions/67666649/lambda-linq-with-contains-criteria-for-multiple-keywords/67666993#67666993) implementation. It generates applies needed predicate automatically. — Svyatoslav Danyliv, Feb 21 '23 at 10:43
@PanagiotisKanavos: Thank you very much for your input. I tried the Library as you mentioned (see edited question above) but somehow, I get the errormessage "Conversion from "LinqKit.ExpressionStarter" in "System.Func" not possible. " and don't know what I am doing wrong. Do you have any idea? — M4SX5, Feb 21 '23 at 14:41
Where do you get the error message? I don't see anywhere a `Func` is used in the code posted - I think you are leaving out some needed code. — NetMage, Feb 21 '23 at 16:09
Thanks for your response. The compiler shows the error message. So in the line: query = query.AsExpandable().Where(predicate); the predicate is red underlined and I can't compile the code. What kind of needed code do I miss? I am thankful for every hint. — M4SX5, Feb 21 '23 at 16:16

score 0 · Accepted Answer · answered Feb 22 '23 at 01:05

Thanks to @Panagiotis Kanavos comment I found a working solution.

I used the LinqKit's Predicate Builder and rewrote my query as follows:

//Initialize query
IQueryable<Movie> query = context.Movies;

//Add actor query
query = query.Where(x => x.Actorid = 1);

//Define variables
var predicate = PredicateBuilder.New<Movie>();

//Create predict
foreach(KeyValuePair<int, int> movieduration in moviedurations){
    int tempMinMovieduration = movieduration.Key;
    int tempMaxMovieduration = movieduration.Value;
    predicate = predicate.Or(p => p.Duration >= tempMinMovieduration && p.Duration <= tempMaxMovieduration );
}

//Add predict to where query
query = query.AsExpandable().Where(predicate);

//Execute query
List<Movie> result = query.ToList();

Multiple OR statements with LINQ

1 Answers1