title: How to combine a linq query dynamically?
body: <p>Suppose I have a table, containing formatted values in a string (<code>nvarchar</code>) column. These values are supposed to be strings separated by some const symbol (let it be a semi-colon <code>;</code>). For example,</p>
  
  <pre><code>12;0;5;dog //four values separated by a semi-colon
  </code></pre>
  
  <p>or</p>
  
  <pre><code>053 //if there is only one value, no semi-colon at the end
  </code></pre>
  
  <p>The separator is always a separator, it can't be a part of the value.</p>
  
  <p>I need to check if there is already a row in that table, with a list of values in that column, which contains at least one of the specified items. In other words I have a list of values:</p>
  
  <pre><code>List&lt;string&gt; list = new List&lt;string&gt;() { "somevalue1", "somevalue2" };
  </code></pre>
  
  <p>the separator:</p>
  
  <pre><code>string separator = ";";
  </code></pre>
  
  <p>And I need to write a linq-to-sql query doing this:</p>
  
  <pre><code>select ... from sometable
  where Value='somevalue1' or Value like 'somevalue1;%' or
        Value like '%;somevalue1' or Value like '%;somevalue1;%'
  
     or Value='somevalue2' or Value like 'somevalue2;%' or 
        Value like '%;somevalue2' or Value like '%;somevalue2;%'
  </code></pre>
  
  <p>It should be mentioned that any of the searched values may contain the other. That is, I may be searching for exactly <code>5</code>, while some row may contain <code>1;15;55</code>. Such row must <strong>not</strong> be a match. While <code>...;5;...</code> or just <code>5</code>, or <code>5;...</code>, or <code>...;5</code> are the matches.</p>
  
  <p>Using linq-to sql I know how to do queries of the following kind: </p>
  
  <pre><code>select ... from sometable where (... or ... or ...) and (... or ...) ...
  </code></pre>
  
  <p>that is</p>
  
  <pre><code>IQueryable&lt;SomeTable&gt; query = dc.SomeTable;
  foreach (string l in list)
  {
      string s = l;
      query = query.Where(b =&gt; b.Value.StartsWith(s + separator) ||
                               b.Value.EndsWith(separator + s) ||
                               b.Value.Contains(separator + s + separator) ||
                               b.Value.Equals(s));
  }
  if (query.Any()) {/*...*/}
  </code></pre>
  
  <p>Obviously the <code>Where</code> statements are joined with <code>AND</code> in the resulting sql query, while I need <code>OR</code> everywhere.</p>
  
  <p>So is there a way to get the query I need within C# code? Or the only way would be to do this with a hand-written query and <a href="http://msdn.microsoft.com/en-us/library/bb361109.aspx" rel="nofollow">DataContext.ExecuteQuery Method</a>? </p>