Creating a dynamic where clause for dynamic keywords or using IQueryable C # Linq

Question

I am trying to write a dynamic where clause using LINQ to return all strings containing any keyword provided in an array of strings. The results do not return, as expected, with what I have so far, and looking at SQL, I see a problem.

IQueryable<comments> query = _db.comments; if (score != null) query = query.Where(x => x.score == score); if (dateFrom != null)3 query = query.Where(x => x.date_created >= dateFrom); if (dateTo != null) query = query.Where(x => x.date_created <= dateTo); if (keywords != null) { //how to use OR for each in array? foreach (var keyword in keywords) { var keywordCondition = keyword; query = query.Where(x => x.text.Contains(keywordCondition)); } } WHERE ([Extent1].[score] = @p__linq__0) AND ([Extent1].[date_created] >= @p__linq__1) AND ([Extent1].[date_created] <= @p__linq__2) AND ([Extent1].[text] LIKE @p__linq__3 ESCAPE '~') AND ([Extent1].[text] LIKE @p__linq__4 ESCAPE '~') 

- should be

 WHERE ([Extent1].[score] = @p__linq__0) AND ([Extent1].[date_created] >= @p__linq__1) AND ([Extent1].[date_created] <= @p__linq__2) AND (([Extent1].[text] LIKE @p__linq__3 ESCAPE '~') OR ([Extent1].[text] LIKE @p__linq__4 ESCAPE '~')) 

I hope someone can help me as I spent a couple of hours looking for a solution.

Thanks in advance

Answer 1

As you may have guessed, this is part of the problem that you will need to change, because it will be an “AND” with all the sentences.

 //how to use OR for each in array? foreach (var keyword in keywords) { var keywordCondition = keyword; query = query.Where(x => x.text.Contains(keywordCondition)); } 

I think you can do this by changing it to:

 query = query.Where(x => keywords.Any(kw => x.text.Contains(kw)); 

Answer 2

You should really learn Dynamic LINQ queries (explained very well here - http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library )

You can specify your request as shown below -

 .Where("Column1 = value1 OR Column2 = value2"); 

Answer 3

If you are trying to build a predicate (condition) dynamically, the easiest and most flexible way I've found is to use PredicateBuilder.

http://www.albahari.com/nutshell/predicatebuilder.aspx

It gives you full control over whether you use and or or to create a query.

Answer 4

I don't know if there is a cleaner way, but change

 foreach (var keyword in keywords) { var keywordCondition = keyword; query = query.Where(x => x.text.Contains(keywordCondition)); } 

to

 query = query.Where(x => keywords.Any(k => x.text.Contains(k)); 

Any() will return bool if x.text contains any of the keywords in the list