CODEWITHSUNDEEP
linqlinq-to-sqldynamic
Victor
Victor

Reputation:

How to dynamically add OR operator to WHERE clause in LINQ

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 :-)

Upvotes: 37

Views: 22028

Answers (4)

midas06
midas06

Reputation: 2001

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.

Upvotes: 1

NinjaNye
NinjaNye

Reputation: 7126

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

Upvotes: 1

Richard
Richard

Reputation: 108975

Either build an Expression<T> yourself, or look at a different route.

Assuming possibleTags is a collection of tags, you can make use of a closure and a join to find matches. This should find any songs with at least one tag in possibleTags:

allSongMatches = allSongMatches.Where(s => (select t from s.Tags
                                            join tt from possibleTags
                                                on t == tt
                                            select t).Count() > 0)

Upvotes: 0

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421978

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);

LinqToSql strange behaviour

Upvotes: 42

Related Questions