Bia Securities

Have you needed to do a complex where clause with a query, but didn’t want to write raw SQL?  That’s where SubSonic expression constraints come in handy.  Say you want to do something like:

select * from Product where IsActive = 1 and (ExpiredOn is null OR ExpiredOn <= ‘01/01/2020′)

Using SubSonic 2.1, you can do the following:

DateTime futureDate = new DateTime(2020, 1, 1);

List<Product> products = DB.Select().From<Product>()
.Where(Product.Columns.IsActive).IsEqualTo(true)
.AndExpression(Product.Columns.ExpiredOn).IsNull().Or(Product.Columns.ExpiredOn).IsLessThanOrEqualTo(futureDate).CloseExpression()
.ExecuteTypedList<Product>();

Basically, the AndExpression part translates to a SQL "and" operator followed by a beginning parentheses.  The CloseExpression translates to the closing parentheses.  Theoretically, you could nest these bad boys as deep as you’d like.  Note: SubSonic also comes with an OrExpression as well.

Hope this helps