Nested OR statements with SubSonic 2.1

Question

I'm getting my head back into SubSonic on a project with v2.1 fairly embedded (meaning we won't be switching it to v3).

I'm ripping through a bunch of method parameters to build a long, but not overly complex, query. At the tail end of this query, I need to add a statement that adds a group of OR statements, something to the equivalent of:

...AND ((DateColumn BETWEEN @StartDate1 AND @EndDate1) OR (DateColumn BETWEEN StartDate2 AND @EndDate2))

Right now I have:

if (criteria.TaxCreditApprovalYear != null && criteria.TaxCreditApprovalYear.Count > 0)
{
    criteria.TaxCreditApprovalYear.ForEach(year => qry.And(Property_Overview.Columns.EffectiveDate)
                                                      .IsBetweenAnd(new DateTime(year, 01, 01),
                                                                    new DateTime(year, 12, 31)));
}

which is giving me a bunch of AND statements. I know that an Or or OrExpression needs to make it's way in there, but I haven't been able to track down where or how to drop that in.

Any thoughts? I'm open to pretty much anything that gets me an appropriate query that doesn't override the other existing AND statements that may or may not already exist.

Answer 1

You could just break it up into a couple of queries, one for each "OR" statement.

ie:

if (criteria.TaxCreditApprovalYear != null && criteria.TaxCreditApprovalYear.Count > 0)
{
    Query qry1 = qry.And(Property_Overview.Columns.EffectiveDate)
             .IsBetweenAnd(new DateTime(year, 01, 01), new DateTime(year, 12, 31)));
    Query qry2 = qry.And(Property_Overview.Columns.EffectiveDate)
             .IsBetweenAnd(StartDate2, EndDate2));
    Query qry3 = qry.And(Property_Overview.Columns.EffectiveDate)
             .IsBetweenAnd(StartDate3, EndDate3));


    criteria.TaxCreditApprovalYear.ForEach(year => qry1);
    criteria.TaxCreditApprovalYear.ForEach(year => qry2);
    criteria.TaxCreditApprovalYear.ForEach(year => qry3);
}

(I have not run the above code, so consider it pseudo-code. Don't you also need a qry.ExecuteScalar() or something??)

Answer 2

There's query.OpenExpression() and query.CloseExpression() to manually create an open and close parens, but I don't know how to add those to the end of an existing set of criteria.

Answer 3

ok, I got it. It's a matter of using AndExpression() instead of And(). I had to initialize the AndExpression with the first instance of the criteria, then just loop through the remaining criteria and attach them with an Or().

// NOTE: Is it just me or does this smell a little?
if (criteria.TaxCreditApprovalYear != null && criteria.TaxCreditApprovalYear.Count > 0)
{

    qry.AndExpression(Property_Overview.Columns.EffectiveDate)
       .IsBetweenAnd(new DateTime(criteria.TaxCreditApprovalYear[0], 01, 01), new DateTime(criteria.TaxCreditApprovalYear[0], 12, 31));

    // skip over the first index since we already set that up above in the AndExpression.
    for (int i = 1; i < criteria.TaxCreditApprovalYear.Count; i++)
    {
        qry.Or(Property_Overview.Columns.EffectiveDate)
           .IsBetweenAnd(new DateTime(criteria.TaxCreditApprovalYear[i], 01, 01), new DateTime(criteria.TaxCreditApprovalYear[i], 12, 31));
    }
}