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

score 1 · Accepted Answer · answered Feb 02 '12 at 20:41

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.

answered Feb 02 '12 at 20:41

marapet

54,856
12
170
184

That get's it close. It doesn't open another parenthesis after the first AND: `WHERE ([dbo].[vSalesRepCommissionGrouped].[UserID] IN (UserID0In1,UserID0In2,UserID0In3) ) AND ([dbo].[vSalesRepCommissionGrouped].[SurgeryDate] >= SurgeryDate2 AND [dbo].[vSalesRepCommissionGrouped].[SurgeryDate] <= SurgeryDate3 OR [dbo].[vSalesRepCommissionGrouped].[SurgeryDate] >= SurgeryDate4 AND [dbo].[vSalesRepCommissionGrouped].[SurgeryDate] <= SurgeryDate5 AND [dbo].[vSalesRepCommissionGrouped].[POReceivedOn] >= POReceivedOn6 ) ) ORDER BY SurgeryDate ASC` – AJ Tatum Feb 02 '12 at 21:25
There is no need for parenthesis around `AND` statements, they have naturally precedence over `OR` comparisons. See also [T-SQL operator precedence](http://msdn.microsoft.com/en-us/library/ms190276.aspx). – marapet Feb 02 '12 at 21:56
Weird. So: `WHERE [UserID] IN (UserID0In1,UserID0In2,UserID0In3) AND ([SurgeryDate] >= SurgeryDate1 AND [SurgeryDate] <= SurgeryDate2 OR [SurgeryDate] >= SurgeryDate3 AND [SurgeryDate] <= SurgeryDate4 AND [POReceivedOn] >= POReceivedOn5 )` **returns a different result than** `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')` – AJ Tatum Feb 02 '12 at 22:59
here's what I'm using to generate the subsonic query: `.Where("UserID").In(new[] { 5, 6, 20 }).AndExpression("SurgeryDate").IsGreaterThanOrEqualTo(new DateTime(2012, 1, 1)).And("SurgeryDate").IsLessThanOrEqualTo(new DateTime(2012, 1, 31)).Or("SurgeryDate").IsGreaterThanOrEqualTo(new DateTime(2011, 12, 1)).And("SurgeryDate").IsLessThanOrEqualTo(new DateTime(2011, 12, 31)).And("POReceivedOn").IsGreaterThanOrEqualTo(new DateTime(2012, 1, 1)).CloseExpression()` – AJ Tatum Feb 02 '12 at 23:00
So I'd say the SubSonic query is ok, but a date interpreted by sql server from a string yields a different result than passing a datetime - maybe because the datetime includes the hour defaulting to 00:00:00 ? Also you'll need to check the SurgerDate in the database (includes hour?) – marapet Feb 02 '12 at 23:14
In other words: Less than or equal to `new DateTime(2012, 12, 31)` probably only includes the first second of the 31st. – marapet Feb 02 '12 at 23:17
Holy crap. Would have never thought of that. I can't believe that caused me so much headache. I changed DateTimes to strings in the format of "yyyy-mm-dd" and it worked. THANK YOU! – AJ Tatum Feb 03 '12 at 21:32

Ben McIntyre · Answer 2 · 2012-03-02T12:41:22.913

Burn me at the stake, but when it gets to this point I usually use an SqlConnection object as pass SQL in direct. I'm not sure that SubSonic 2 is up to these intricacies.

I also don't think that the parentheses you are using in the subsonic query are actually doing anything. They may affect the parse order of the criteria, but not the construction of the final SQL statement (at least, not in sensibly controllable ways).

SubSonic 3 brought the AndAlso operator to simulate parentheses, but even that's pretty crude. LINQ (supported in SubSonic 3) is probably the only way to do this reliably.

score 0 · Answer 3 · answered Feb 02 '12 at 20:21

I can't test right now but please see if this query works:

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

Thanks for the help, but that doesn't work either. Here's the where clause: `WHERE ([dbo].[vSalesRepCommissionGrouped].[SurgeryDate] >= SurgeryDate0 AND [dbo].[vSalesRepCommissionGrouped].[SurgeryDate] <= SurgeryDate1 ) OR ([dbo].[vSalesRepCommissionGrouped].[SurgeryDate] >= SurgeryDate3 AND [dbo].[vSalesRepCommissionGrouped].[SurgeryDate] <= SurgeryDate4 AND [dbo].[vSalesRepCommissionGrouped].[POReceivedOn] >= POReceivedOn5 ) AND ([dbo].[vSalesRepCommissionGrouped].[UserID] IN (UserID7In1,UserID7In2,UserID7In3) ) ` — AJ Tatum, Feb 02 '12 at 21:28

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

3 Answers3