c# - ServiceStack ORMLite Sql Server *optional filter -

i need sql in ormlite sql server: (if pass 0 in parameters remove filter, in sql:

declare @departmentid int = 0;  declare @projecttaskstatusid int = 0;  select * projecttask t join project p on p.projectid = t.projectid        (p.departmentid = @departmentid or @departmentid = 0) ,  (t.projecttaskstatusid = @projecttaskstatusid or @projecttaskstatusid = 0) 

i've created code below not working, best way in ormlite sql server?

dbcon.loadselectasync(x => (x.project.departmentid == departmentid || departmentid == 0) && (x.projecttaskstatusid == projecttaskstatusid || projecttaskstatusid == 0));

i make work using code below (but using lambda , not straight in ormlite:

var tasks = await dbcon.loadselectasync<projecttask>(x => x); return tasks.where(x => (departmentid == 0 || x.project.departmentid.equals(departmentid)) && (projecttaskstatusid == 0 || x.projecttaskstatusid.equals(projecttaskstatusid))); 

after of guys solution below, however, think costly, because can't use loadreferences selectasync, , have foreach load manually each reference:

var query = dbcon.from<projecttask>()                     .join<projecttask, project>((pt, p) => pt.projectid == p.id)                     .where<project>(p => p.departmentid == departmentid || departmentid == 0)                     .and<projecttask>(pt => pt.projecttaskstatusid == projecttaskstatusid || projecttaskstatusid == 0);  var tasks = await dbcon.selectasync(query);  // load references foreach (var item in tasks) {     if (item.projectid > 0)         item.project = await dbcon.singlebyidasync<project>(item.projectid);      if (item.assignedtoid > 0)         item.assignedto = await dbcon.singlebyidasync<employee>(item.assignedtoid);      if (item.requestedbyid > 0)         item.requestedby = await dbcon.singlebyidasync<employee>(item.requestedbyid);      if (item.projecttaskstatusid > 0)         item.projecttaskstatus = await dbcon.singlebyidasync<projecttaskstatus>(item.projecttaskstatusid); }  return tasks; 

i don't have code in front of me right might off base bit on syntax, think following code translate equivalent query.

var query = db.from<projecttask>()               .join<projecttask, project>((pt, p) => pt.projectid == p.projectid)               .where<project>(p => p.departmentid == departmentid || departmentid == 0)               .and<projecttask>(pt => pt.projecttaskstatusid == statusid || statusid == 0);  var tasks = await dbcon.selectasync<projecttask>(query); 

disclaimer: servicestack.ormlite version 4.0+ new nifty sqlexpression classes.

update:

ok, see you're trying now.
yes, loading references in loop not efficient solution. i'm not sure why loadselect methods doesn't work (you might want report possible bug if have reproduction test it), there may workaround that.

if dig ormlite source code , check can see being done under covers of loadselect query first being run , ormlite iterates on model definition , issues query of form

select columns table id in (originalquery)

for each of references. takes result of query , hooks results references. ormlite issue several queries here when using loadselect, though 1 query per reference instead of 1 query per reference per object better.

you can strategy manually. though bit of hassle... i'm not giving code right since don't have computer vs in front of me right , bit more advanced don't think i'll able guess this.

however, need of references? i'm guessing now, looks lot of data loading in 1 go. need of references , columns or need couple of them? if need couple of them may want go join custom model columns need instead. performant option since able in 1 single query , transfer data need instead of everything. don't know how using data might not option. :)

but said, may have found bug in loadselect! i'd highly recommend add issue tracker , see if they'll fix - in experience servicestack fast in responding , fixing bugs. if give them reproducible unit test (which extract code pretty easily).

https://github.com/servicestack/issues/issues