title: Why does this LINQ-to-SQL query get a NotSupportedException?
body: <p>The following <strong>LINQ</strong> statement:</p>
  
  <pre><code>public override List&lt;Item&gt; SearchListWithSearchPhrase(string searchPhrase)
  {
      List&lt;string&gt; searchTerms = StringHelpers.GetSearchTerms(searchPhrase);
  
      using (var db = Datasource.GetContext())
      {
          return (from t in db.Tasks
                  where searchTerms.All(term =&gt; 
                      t.Title.ToUpper().Contains(term.ToUpper()) &amp;&amp;
                      t.Description.ToUpper().Contains(term.ToUpper())) 
                  select t).Cast&lt;Item&gt;().ToList();
      }
  }
  </code></pre>
  
  <p>gives me this <strong>error</strong>:</p>
  
  <blockquote>
    <p>System.NotSupportedException: Local
    sequence cannot be used in LINQ to SQL
    implementation of query operators
    except the Contains() operator.</p>
  </blockquote>
  
  <p>Looking around it seems my only option is to get <strong>all</strong> my items <strong>first</strong> into a generic List, then do a LINQ query on that.</p>
  
  <p><strong>Or is there a clever way to rephrase the above LINQ-to-SQL statement to avoid the error?</strong> </p>
  
  <h1>ANSWER:</h1>
  
  <p>Thanks Randy, your idea helped me to build the following solution. It is not elegant but it solves the problem and since this will be code generated, I can handle up to e.g. 20 search terms without any extra work:</p>
  
  <pre><code>public override List&lt;Item&gt; SearchListWithSearchPhrase(string searchPhrase)
  {
      List&lt;string&gt; searchTerms = StringHelpers.GetSearchTerms(searchPhrase);
  
      using (var db = Datasource.GetContext())
      {
  
          switch (searchTerms.Count())
          {
              case 1:
                  return (db.Tasks
                       .Where(t =&gt;
                           t.Title.Contains(searchTerms[0])
                           || t.Description.Contains(searchTerms[0])
                           )
                       .Select(t =&gt; t)).Cast&lt;Item&gt;().ToList();
              case 2:
                  return (db.Tasks
                       .Where(t =&gt;
                           (t.Title.Contains(searchTerms[0])
                           || t.Description.Contains(searchTerms[0]))
                           &amp;&amp;
                           (t.Title.Contains(searchTerms[1])
                           || t.Description.Contains(searchTerms[1]))
                           )
                       .Select(t =&gt; t)).Cast&lt;Item&gt;().ToList();
              case 3:
                  return (db.Tasks
                       .Where(t =&gt;
                           (t.Title.Contains(searchTerms[0])
                           || t.Description.Contains(searchTerms[0]))
                           &amp;&amp;
                           (t.Title.Contains(searchTerms[1])
                           || t.Description.Contains(searchTerms[1]))
                           &amp;&amp;
                           (t.Title.Contains(searchTerms[2])
                           || t.Description.Contains(searchTerms[2]))
                           )
                       .Select(t =&gt; t)).Cast&lt;Item&gt;().ToList();
              default:
                  return null;
          }
      }
  }
  </code></pre>