none
How to dynamically add OR operator to WHERE clause RRS feed

  • Question

  • Hello friends,

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

    Thank you very, very much in advance!

    Victor
    Wednesday, April 22, 2009 4:04 PM

Answers

All replies

  • Hello,

    From where you are now, your best bet is probably to create a method:

    private bool MyContainsMethod(Song SongsVar )
    {
               foreach(string str in strArray)
               {
                 if(SongsVar.Tags.Contains(str))
    return true;
    } return false; }

    and then call it:

    allSongMatches = allSongMatches.Where(SongsVar => MyContainsMethod(SongsVar ));

    I hope this helps...

    LS

    • Proposed as answer by Lenny S Wednesday, April 22, 2009 4:26 PM
    Wednesday, April 22, 2009 4:26 PM
  • Hey Leonardo,

    Thanks for the quick reply. Your suggestion was very interesting, but unfortunately it does not work, because LINQ to SQL does not know how to translate the MyContainsMethod (I get a "method has no supported translation to SQL" exception when I run the code).

    Any other thoughts?

    Thanks,
    Victor
    Wednesday, April 22, 2009 5:34 PM
  • Sorry mate,

    It was worth a try...



    LS
    • Proposed as answer by Lenny S Thursday, April 23, 2009 9:35 AM
    Wednesday, April 22, 2009 7:28 PM
  • Ideally, you could do this.

    string[] songTags = new [] { "foo1", "foo2" };

    var query = allSongMatches.Where(song => songTags.Any(tag => song.Tags.Contains(tag));

    Yet, L2S won't translate Any over a local collection like it does with Contains.


    Wayward LINQ Lacky
    • Proposed as answer by Lenny S Thursday, April 23, 2009 7:28 AM
    Wednesday, April 22, 2009 10:33 PM
    Moderator
  • Leonardo, both of the links you posted offer promising approaches to the problem. I will investigage this further and will post a solution here if I come up with one.

    Many thanks!
    Victor
    Thursday, April 23, 2009 3:32 PM
  • Matt, I know... this is so frustrating. Especially since the code builds and you don't find the problem until you run it. I can only hope that future versions of LINQ will support the kind of approach you're suggesting.

    Victor
    Thursday, April 23, 2009 3:34 PM
  • Here's the solution to the problem, answered in another forum:

    http://stackoverflow.com/questions/782339/how-to-dynamically-add-or-operator-to-where-clause-in-linq

    Thanks for all your help!
    Thursday, April 23, 2009 4:25 PM