CODEWITHSUNDEEP
c#sql-serverormliteormlite-servicestack
Roger
Roger

Reputation: 1631

ServiceStack ORMLite Sql Server *optional filter

I need to do this SQL in ORMLite Sql Server: (If I pass 0 in the parameters then I remove the filter, as in the SQL:

declare @departmentId int = 0; 
declare @projectTaskStatusId int = 0; 
select * from ProjectTask t
join Project p on p.ProjectId = t.ProjectId     
where  
(p.DepartmentId = @departmentId or @departmentId = 0) and 
(t.ProjectTaskStatusId = @projectTaskStatusId or @projectTaskStatusId = 0)

I've created this code below but It is not working, what is the best way to do this where in ORMLite SQL Server?

dbCon.LoadSelectAsync(x => (x.Project.DepartmentId == departmentId || departmentId == 0) && (x.ProjectTaskStatusId == projectTaskStatusId || projectTaskStatusId == 0));

I could make it work using the code below (but this is using Lambda and 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 the help of you guys I could do the solution below, however, I think it is costly, because I can't use the LoadReferences only the SelectAsync, and then I have to do a foreach to 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 the 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;

Upvotes: 0

Views: 710

Answers (1)

wasatz
wasatz

Reputation: 4278

I don't have the code in front of me right now so I might be off base a bit on the syntax, but I think that the following code will translate into an 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: This is for Servicestack.OrmLite version 4.0+ with its new nifty SqlExpression classes.

Update:

Ok, I see what you're trying to do now.
Yes, loading the references in a for loop like that is not very efficient solution. I'm not sure why the LoadSelect methods doesn't work (you might want to report that as a possible bug if you have a good reproduction test for it), but there may be a workaround for that.

If you dig into the OrmLite source code and check what it actually does you can see that what is actually being done under the covers of LoadSelect is that the query is first being run and then OrmLite iterates over the model definition and issues a query of the form

SELECT columns FROM table WHERE id IN (originalquery)

for each of the references. Then it takes the result of that query and hooks up the results to the references. So OrmLite will actually issue several queries here when using LoadSelect, though it will be 1 query per reference instead of 1 query per reference per object which is much better.

You can do this strategy manually. Though it will be a bit of a hassle... I'm not giving you any code for this right now since I don't have a computer with VS in front of me right now and this is a bit more advanced so I don't think I'll be able to guess this.

However, do you actually need all of those references? I'm just guessing now, but that looks like a lot of data that you are loading in one go. Do you really need all of those references and columns or do you just need a couple of them? If you only really need a couple of them you may want to go with a join into a custom model with only the columns that you actually need instead. That will be the most performant option since you will be able to do that in one single query and only transfer the data that you actually need instead of everything. But I don't know how you are using this data so that might not be a good option. :)

But as said, you may have found a bug in LoadSelect! I'd highly recommend you to add it to the issue tracker and see if they'll fix it - in my experience ServiceStack is really fast in responding and fixing bugs. Especially if you give them a reproducible unit test (which you could probably extract from your code pretty easily).

https://github.com/ServiceStack/Issues/issues

Upvotes: 1

Related Questions