title: LINQ to SQL query fails using two Contains statements
body: <p>I have two tables, <code>DH_MASTER</code> and <code>DH_ALIAS</code>.  <code>DH_MASTER</code> contains information about a person, including their name.  <code>DH_ALIAS</code> contains AKA records about the person.  The tables are linked by the <code>Operator</code> field which is a primary key in <code>DH_MASTER</code>.</p>
  
  <p>The users want to search by the name stored in <code>DH_MASTER</code> as well as search through all of their known aliases.  If any matches are found in either <code>DH_MASTER</code> or <code>DH_ALIAS</code> then the <code>DH_MASTER</code> entity should be returned.</p>
  
  <p>I created the query below which should give the results I described (return any <code>DH_MASTER</code> rows where the <code>DH_MASTER.Name == name</code> or <code>DH_MASTER.DH_ALIAs(n).Name == name</code>).</p>
  
  <p>It works fine if I use only ONE of the <code>.Contains</code> lines.  It doesn't matter which one I use.  But the execution fails when I try to use BOTH at the same time.</p>
  
  <pre><code>
      qry = From m In Context.DH_MASTERs _
            Where (m.Name.Contains(name)) _
                OrElse ((From a In m.DH_ALIAs _
                         Where a.Name.Contains(name)).Count() > 0) _
            Select m
  </code></pre>
  
  <p>The LinqToSQL Query evaluates to the following SQL code (as displayed in the SQL Server Query Visualizer)</p>
  
  <pre><code>
  SELECT [t0].[Operator], [t0].[Name], [t0].[Version]
  FROM [DHOWNER].[DH_MASTER] AS [t0]
  WHERE ([t0].[Name] LIKE %smith%) OR (((
      SELECT COUNT(*)
      FROM [DHOWNER].[DH_ALIAS] AS [t1]
      WHERE ([t1].[Name] LIKE %smith%) AND ([t1].[Operator] = [t0].[Operator])
      )) > 0)
  </code></pre>
  
  <p>EDIT:  Checking the "Show Original" box in the Query Visualizer reveals the parameterized query as expected so this block of text below should be ignored.</p>
  
  <blockquote>
  I don't know if this is a problem or not but the `.Contains` evaluates to a `LIKE` expression (which is what I expect to happen) but the parameter is not encapsulated in apostrophes.
  </blockquote>
  
  <p>The interesting thing is that if I copy/paste the SQL Query into SQL 2005 Query Analyzer and add the apostrophes around the <code>LIKE</code> parameters, it runs just fine.  In fact, it's lightning quick (blink of an eye) even with more than 2 million rows.</p>
  
  <p>But when the LINQ query runs, the web app locks up for about 31 seconds before it finally fails with this error on gv.DataBind:  <code>Exception has been thrown by the target of an invocation.</code></p>
  
  <p>With this innerException:  <code>Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.</code></p>
  
  <p>Does anyone know why this happens and how the behavior can be worked around?  It's driving me nuts because the LinqToSql-generated SQL runs fine in query analyzer!</p>
  
  <p><strong>Update:</strong></p>
  
  <p>I have refactored my code based on the techniques in the answer.  This works!</p>
  
  <pre><code>qry = From m In qry _
   Where m.Name.Contains(name) OrElse _ 
   m.DH_ALIAs.Any(Function(aliasRec) aliasRec.Name.Contains(name)) _ 
  Select m
  </code></pre>