title: LINQ string[] against multiple fields
body: <p>Lets say I have a table dataContext.Customer with the following fields</p>
  
  <pre><code>    FName    varchar
      LName    varchar
      Phone    varchar
      DOB      datetime
      Address  varchar
  </code></pre>
  
  <p>The table is filled with some sample data, lets say:</p>
  
  <pre><code>    John | Smith | 3051112222 | 01/01/1978 | Roosevelt Av 787
      Aron | Frank | 7871112222 | 01/01/1979 | Lambda Street 305
      Dick | Bush  | 9512221111 | 01/01/1980 | John Street 1
      John | Allen | 7872222222 | 01/01/1981 | Liberty Av 555
  </code></pre>
  
  <p>We also have a string array with an arbitrary number of elements, for example:</p>
  
  <pre><code>    search[0] = "1978"
      search[1] = "John"
  </code></pre>
  
  <p>I need a LINQ query that will compare each field of the table against each item in the string array incrementally using "contains" or "any" (meaning LIKE in SQL) and only return the rows that match all the given conditions in a record, based on the previous search[] example the LINQ query should return only record #1.</p>
  
  <p>Another example can be:</p>
  
  <pre><code>    search[0] = "Bush"
      search[1] = "111"
      search[2] = "John"
  </code></pre>
  
  <p>and only record #3 should by returned. Finally for:</p>
  
  <pre><code>    search[0] = "John"
  </code></pre>
  
  <p>Records #1, #3 and #4 should be returned (I think the idea is clear)</p>
  
  <p>There's a question on how to compare a string[] against field in:
  <a href="https://stackoverflow.com/questions/1757214/linq-entity-string-field-contains-any-of-an-array-of-strings">LINQ: Entity string field contains any of an array of strings</a></p>
  
  <p>If the answer is a 50 lines C# routine I prefer to solve this directly in the database via stored procedure.</p>
  
  <p>It will be awesome if there is some kind of "reflection" trick to iterate all the field on dataContext.Customers while doing the query (obviously the real table don't have 5 fields).</p>
  
  <p>Performance is not a problem.</p>
  
  <p>I'm pretty sure this cannot be done in a single LINQ line because of the logic needed for the multiple match, but it never hurt to ask, much less to learn anything new :)</p>
  
  <p>UPDATE:
  Ok, here's a simple SQL code that will accomplish the task. Note that I have cut the amount of search variables to just 2 for clarity. In the real life scenario we can limit the amount of arguments to 10 search parameters. I deliberately did not use functions (well, except for CONVERT) to keep the SQL as simple as possible to see if there is any way to accomplish this in LINQ. Here's the SQL:</p>
  
  <pre><code>    declare @_SEARCH1 varchar(1000)
      select  @_SEARCH1 = 'John'
      declare @_SEARCH2 varchar(1000)
      select  @_SEARCH2 = '111'
  
      select  *
      from    CUSTOMER
      where
              FName + ' ' + LName + ' ' + Phone + ' ' + CONVERT(varchar, DOB, 101) + ' ' + Address like '%'+@_SEARCH1+'%'
      and     FName + ' ' + LName + ' ' + Phone + ' ' + CONVERT(varchar, DOB, 101) + ' ' + Address like '%'+@_SEARCH2+'%'
  </code></pre>
  
  <p>So the question is, is there a way to write a LINQ that will generate this simple SQL? (please note that the comparison is done in the database via 'LIKE', not in the application)</p>
  
  <p>UPDATE 2: Although solutions like the one from Francisco will generate the "LIKE" statement it will fail doing the comparison. The other solutions that pulls all the data from the table to the webserver will do the match correctly, but is totally impractical.</p>
  
  <p>Accepted answer to RUNE FS since is the cleanest solution and will work will any number of fields.</p>