Subsonic Query Condition A AND ((Condition B AND Condition C) OR (Condition D AND Condition E AND Condition F)

Question

I'm close to pulling out the rest of my hair because I cannot figure out how to do the following T-SQL Query in SubSonic 2.2:

SELECT  SalesRep, Location, InvoiceNumber, PONumber, POReceivedOn, SurgeryDate, Surgeon
FROM    dbo.vSalesRepCommissionGrouped AS vsrcg
WHERE   UserID IN ( 5, 6, 20 )
    AND ( ( SurgeryDate >= '2012-01-01'
            AND SurgeryDate <= '2012-01-31'
          )
          OR ( SurgeryDate >= '2011-12-01'
               AND SurgeryDate <= '2011-12-31'
               AND POReceivedOn >= '2012-01-01'
             )
        )
ORDER BY SurgeryDate ASC

I've tried the following SubSonic Select in at least 10 different ways, but I cannot get it to produce the same results.

new Select("SalesRep, Location, InvoiceNumber, PONumber, POReceivedOn, SurgeryDate, Surgeon")
                    .From(VSalesRepCommissionGrouped.Schema)
                    .WhereExpression("UserID").In(new[] { 5, 6, 20 })
                    .AndExpression("SurgeryDate").IsGreaterThanOrEqualTo(BeginDate).And("SurgeryDate").IsLessThanOrEqualTo(EndDate)
                    .Or("SurgeryDate").IsGreaterThanOrEqualTo(BeginDate.AddMonths(-1)).And("SurgeryDate").IsLessThanOrEqualTo(
                        EndDate.AddMonths(-1)).And("POReceivedOn").IsGreaterThanOrEqualTo(BeginDate).CloseExpression()
                    .OrderAsc("SurgeryDate");

Could someone please point me in the right direction?

Thank you! - Andrew

Answer 1

Actually, you're SubSonic select should be fine, just use a Where instead of a WhereExpression (what SQL do you get?).

I also recommend using the strongly typed columns of SubSonic instead of magic strings - that's one of the advantages of using SubSonic.