title: Complex Linq-to-SQL where condition help
body: <p>I have a perplexing SQL select statement (ugly) that I'm trying to write in LINQ. I'm working to get a single statment that will execute, not pre-selected data into lists that I have to send back to the server.</p>
  
  <pre><code>DECLARE @StartDate DATETIME ;
  DECLARE @EndDate DATETIME ;
  SET @pStartDate = '20100501'
  SET @pEndDate = '20100531'
  
  SELECT r.company ,r.trandate ,r.currency ,r.account ,r.credit ,r.debit
  FROM   dbo.Register r
  INNER JOIN dbo.CompanyList c ON r.company = c.company
  WHERE  
  r.trandate BETWEEN @pStartDate AND @pEndDate
  AND LEN(r.currency) &gt; 0
  AND ( 
      ( r.account = 'XXX-ZZZ' )
      OR
      ( LEFT(r.account, 3) IN ( SELECT LEFT(code, 3) FROM dbo.investments ))
      OR 
      ( r.account IN ( 
          SELECT account FROM dbo.CompanyInfo WHERE company = r.company
                     AND ( ( dateclosed IS NULL )
                     OR dateclosed &gt;= @pStartDate) ) )
      )
  </code></pre>
  
  <p>This is an example that contains the problem code - a WHERE clause with a triple OR expression. I've tried using three different queries then concat() or union() which returns incorrect reccord count because a record may match multiple expressions. I'm going to try rearranging logic and create a new TSQL version that may help me find a solution in LINQ. </p>
  
  <p>Ideas welcome.</p>