LINQ contains one match from array of strings

Question

Having trouble getting this to work:

    /// <summary>
    /// Retrieve search suggestions from previous searches
    /// </summary>
    public static string[] getSearchSuggestions(int SectionID, string Query)
    {
        string[] Suggestions;
        string[] Words = Query.Split(' ');

        using (MainContext db = new MainContext())
        {
            Suggestions = (from c in db.tblSearches
                        where c.SectionID == SectionID &&
                        Words.Any(w => c.Term.Contains(w))
                        select c.Term).ToArray();
        }

        return Suggestions;
    }

I get:

System.NotSupportedException: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

I want to return records where the field c.Term contains any of the words in the Words array. I would also like to have it ordered by the total number of matches, but that seems really hard to do! I found this MSDN. But I can't get it to work with my query either. Also found this but it's not working.

Answer 1

Ok, after plugging away enough at it I realized that the problem wasn't the Any or the Contains. Linq to SQL doesn't like you combining the local sequence (words) with the SQL collection (db.tblSearches). So in order to accomplish this, you have to break it out into 2 separate queries.

public static string[] getSearchSuggestions(int SectionID, string Query)
{
    string[] Suggestions;
    string[] Words = Query.Split(' ');

    using (MainContext db = new MainContext())
    {
        string[] all = (from c in db.tblSearches
                    where c.SectionID == SectionID
                    select c.Term).ToArray();

        Suggestions = (from a in all
                       from w in Words
                       where a.Contains(w)
                       select a).Distinct().ToArray();


    }

    return Suggestions;
}

Keep in mind, that in the second query, the Contains is case sensitive, so you might have to add a case-insensitive extension method or go old school and kick them .ToUpper(). I ran this in 4.0 on one of my contexts and it returned all 88 strings correctly (out of a possible 9814). Though it was a thorough PITA. Definite +1 on this question.

Edit: Added .Distinct() to the final answer.

Answer 2

First convert your array to a list,

 List<string> wordList = Words.ToList();

then change your linq query as follows:

    Suggestions = (from c in db.tblSearches                           
where c.SectionID == SectionID &&                           
Words.Contains(c.Term)                           
select c.Term).ToArray();  

I think I see your problem though. In your original query you are using c.Term.Contains()... Contains is an extension method that needs to be called on an object that implements Enumerable so you cannot call it on a field you are getting back from your database call. That is why the other user that answered your question said you needed to flip things around in your Contains, because it is never going to let you make that call on c.Terms.