How to build a dynamic query with SubSonic

Question

I need to build (and run) a query based on different of conditions. When I was building a literal SQL to run, this would mean concatenating strings to build the SQL dynamically.

How would I do it with SubSonic? If for example I need to add a WHERE clause in some case and an ORDER BY in other cases. How would the code to build and run this query look?

Answer 1

Here's an example with v2.1+ and BaseClass set to RepositoryRecord:

var q = DB.Select().From<Product>();

if (someCondition) 
    q.Where(Product.ProductIdColumn).IsEqualTo(1);

if (order == "ASC")
    q.OrderAsc(Product.Columns.ProductId)
else
    q.OrderDesc(Product.Columns.ProductId)

var results = q.ExecuteAsCollection<ProductCollection>();