title: EF filtering/searching with multiple words
body: <p>I have a simple custom table with a search/filter field. I leave the implementation of the search up to each use of the table.</p>
  
  <p>So let's say I have users in my table and I want to search for them. I want to search both in users firstname, lastname and also any role they are in.</p>
  
  <p>This would probably do the trick</p>
  
  <pre><code>            searchString = searchString.ToLower();
  
              query = query.Where(
                  x =&gt;
                      x.FirstName.ToLower().Contains(searchString)
                      ||
                      x.LastName.ToLower().Contains(searchString)
                      ||
                      x.Roles.Any(
                          role =&gt;
                              role.Name.ToLower().Contains(searchString)
                          )
                  );
  </code></pre>
  
  <p>But now I want to search/filter on multiple words. First I get an array of all separate words.</p>
  
  <pre><code>            var searchStrings = searchString.ToLower().Split(null);
  </code></pre>
  
  <p>I tried the following but it does not fulfill my requirements listed further down as it returns any user where any word is matched in any field. I need that all words are matched (but possibly in different fields). Se below for more details.</p>
  
  <pre><code>            query = query.Where(
                  x =&gt;
                      searchStrings.Any(word =&gt; x.FirstName.ToLower().Contains(word))
                      ||
                      searchStrings.Any(word =&gt; x.LastName.ToLower().Contains(word))
                      //snipped away roles search for brevity
                  );
  </code></pre>
  
  <p>First let me produce some data</p>
  
  <h2>Users (data)</h2>
  
  <p>Billy-James Carter is admin and manager</p>
  
  <p>James Carter is manager</p>
  
  <p>Billy Carter has no role</p>
  
  <h2>Cases</h2>
  
  <p>If my search string is "billy car" I want Billy-James and Billy returned but not James Carter (so all words must match but not on same field).</p>
  
  <p>If my search string is "bil jam" or even "bil jam car" I only want Billy-James returned as he is the only one matching all terms/words. So in this the words bil and jam were both found in the FirstName field while the car term was found in the LastName field. Only getting the "car" part correct is not enough and James is not returned.</p>
  
  <p>If I search for "car man" Billy-James and James are both managers (man) and named Carter and should show up. should I search for "car man admi" then only Billy-James should show up.</p>
  
  <p>I am happy to abandon my current approach if better is suggested.</p>