Complex Linq-to-SQL where condition help

Question

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.

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) > 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 >= @pStartDate) ) )
    )

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.

Ideas welcome.

Answer 1

Since LINQ-to-SQL supports TSQL via ExecuteQuery - why re-write something that works? Complex queries may well deserve a bit of hand-cranking. I would just leave "as is", and simply substitute:

SET @pStartDate = {0}
SET @pEndDate = {1}

which will be injected when you call

var data = ctx.ExecuteQuery<RegisterQueryResult>(tsql, startDate, endDate);

Answer 2

I'm not sure SubString will translate in Linq to SQL so your LEFT parts probably won't translate at all. In fact, I'm relatively certain they won't so Marc's solution is probably best if you simply want to get over the hump. That said, the following might work if you assume a context object has been created and you're mostly taking the defaults on table and column names.

var registers = from reg in context.Registers
                where reg.Trandate >= pStartDate && reg.Trandate <= pEndDate
                && ((reg.Account == regValue)
                    || ((from investment in context.Investments
                         select investment.Code.Substring(0,3)).Contains(reg.Account.Substring(0,3)))
                    || ((from cInfo in context.CompanyInfo
                         where cInfo.Company == reg.Company && ((cInfo.DateClosed == null) || cInfo.DateClosed.Value == pStartDate)
                        select cInfo.Account).Contains(reg.Account)))
                select New {reg.Company, reg.Trandate, reg.Currency, reg.Account, reg.Credit, reg.Debit};