c# - Parameterizing a ServiceStack custom SQL query -

i have following custom sql query ormlite:

var results = db.select<tuple<customer,purchase>>(@"select c.*, 0 eot, p1.*     customer c     join purchase p1 on (c.id = p1.customer_id)     left outer join purchase p2 on (c.id = p2.customer_id ,          (p1.date < p2.date or p1.date = p2.date , p1.id < p2.id))     p2.id null;"); 

what best way add optional clauses to, instance, filter given customer name field when field has value or add pagination query?

if you're working custom sql need construct additional queries yourself, e.g:

var sql = @"select c.*, 0 eot, p1.*     customer c     join purchase p1 on (c.id = p1.customer_id)     left outer join purchase p2 on (c.id = p2.customer_id ,          (p1.date < p2.date or p1.date = p2.date , p1.id < p2.id))     p2.id null";  //string name = "customer name"; string name = null; if (name != null) {     sql += "\nand name = @name"; }  int? limit = 100; if (limit != null) {     sql += $"\nlimit {limit}"; }  var results = db.select<tuple<customer,purchase>>(sql, new { name }); 

a live example of available on gistlyn.