How to dynamically add OR operator to WHERE clause in LINQ

Question

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

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

http://stackoverflow.com/questions/658818/linqtosql-strange-behaviour/658840#658840

Answer 2

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)