How to dynamically add OR operator to WHERE clause in LINQ
Solution 1
You can use the PredicateBuilder
class:
var searchPredicate = PredicateBuilder.False<Songs>();
foreach(string str in strArray)
{
var closureVariable = str; // See the link below for the reason
searchPredicate =
searchPredicate.Or(SongsVar => SongsVar.Tags.Contains(closureVariable));
}
var allSongMatches = db.Songs.Where(searchPredicate);
Solution 2
There is another, somewhat easier method that will accomplish this. ScottGu's blog details a dynamic linq library that I've found very helpful in the past. Essentially, it generates the query from a string you pass in. Here's a sample of the code you'd write:
Dim Northwind As New NorthwindDataContext
Dim query = Northwind.Products _
.Where("CategoryID=2 AND UnitPrice>3") _
.OrderBy("SupplierId")
Gridview1.DataSource = query
Gridview1.DataBind()
More info can be found at scottgu's blog here.
Solution 3
I recently created an extension method for creating string searches that also allows for OR
searches. Blogged about here
I also created it as a nuget package that you can install:
http://www.nuget.org/packages/NinjaNye.SearchExtensions/
Once installed you will be able to do the following
var result = db.Songs.Search(s => s.Tags, strArray);
If you want to create your own version to allow the above, you will need to do the following:
public static class QueryableExtensions
{
public static IQueryable<T> Search<T>(this IQueryable<T> source, Expression<Func<T, string>> stringProperty, params string[] searchTerms)
{
if (!searchTerms.Any())
{
return source;
}
Expression orExpression = null;
foreach (var searchTerm in searchTerms)
{
//Create expression to represent x.[property].Contains(searchTerm)
var searchTermExpression = Expression.Constant(searchTerm);
var containsExpression = BuildContainsExpression(stringProperty, searchTermExpression);
orExpression = BuildOrExpression(orExpression, containsExpression);
}
var completeExpression = Expression.Lambda<Func<T, bool>>(orExpression, stringProperty.Parameters);
return source.Where(completeExpression);
}
private static Expression BuildOrExpression(Expression existingExpression, Expression expressionToAdd)
{
if (existingExpression == null)
{
return expressionToAdd;
}
//Build 'OR' expression for each property
return Expression.OrElse(existingExpression, expressionToAdd);
}
}
Alternatively, take a look at the github project for NinjaNye.SearchExtensions
as this has other options and has been refactored somewhat to allow other combinations
Admin
Updated on July 25, 2022Comments
-
Admin almost 2 years
I have a variable size array of strings, and I am trying to programatically loop through the array and match all the rows in a table where the column "Tags" contains at least one of the strings in the array. Here is some pseudo code:
IQueryable<Songs> allSongMatches = musicDb.Songs; // all rows in the table
I can easily query this table filtering on a fixed set of strings, like this:
allSongMatches=allSongMatches.Where(SongsVar => SongsVar.Tags.Contains("foo1") || SongsVar.Tags.Contains("foo2") || SongsVar.Tags.Contains("foo3"));
However, this does not work (I get the following error: "A lambda expression with a statement body cannot be converted to an expression tree")
allSongMatches = allSongMatches.Where(SongsVar => { bool retVal = false; foreach(string str in strArray) { retVal = retVal || SongsVar.Tags.Contains(str); } return retVal; });
Can anybody show me the correct strategy to accomplish this? I am still new to the world of LINQ :-)
-
Admin about 15 yearsThanks Mehrdad, your solution came in record time and worked like a charm! I was unaware of the PredicateBuilder class. What a handy feature! I also appreciate your comment on the use of a temporary variable to hold the strings...that would have driven me nuts! Victor
-
Admin about 15 yearsHey Richard, I appreciate your feedback. For some reason, VS did not like the syntax of the code you proposed...am I missing something obvious?
-
Richard about 15 yearsQuite possibly has an error... would need a little time to put together data/test harness to confirm. But have added select clause to inner comprehension expression... which is required (oops).
-
Daniel over 7 yearsI Got Confused about the False expression to create the var searchPredicate, This need to be false? or anything else would fit? (Talking about the PredicateBuilder.False<Songs>() )
-
mmx over 7 yearsIt needs to be false, because the constructed query needs to look like "false || contains x || contains y || ..."