I'm attempting to accomplish paging in my ServiceStack (4.0.50) services, and I'm running into an issue when the query has multiple joins using LoadSelect.
In order to filter results for security requirements, my Get method pulls info from a custom user session and builds a query. Something like this:
public QueryResponse<Foo> Get(Foos request)
{
var session = SessionAs<CustomUserSession>();
var q = Db.From<Foo>().LeftJoin<Foo, User>((s, u) => s.UserId == u.Id);
// Also tried this with no success
//q.Skip(request.Skip);
//q.Take(request.Take);
q.Limit(request.Skip, request.Take);
// I do filtering based on the request object for searching
if (request.Price.HasValue)
{
q.And(s => s.Price == request.Price);
}
// ... and so on ...
// Then I do filtering based on roles
if (session.HasRole("OrgAdmin"))
{
// Do some filtering for their organization
}
else if (session.HasRole("SiteManager"))
{
// More filtering here
}
// Ordering also...
if (request.OrderBy.IsNullOrEmpty())
{
q.OrderByFieldsDescending(request.OrderByDesc);
}
else
{
q.OrderByFields(request.OrderBy);
}
var response = new QueryResponse<Foo>
{
// This works fine
Total = (int)Db.Count(q),
// === Error here ===
Results = Db.LoadSelect(q)
};
return response;
}
The count always works fine, and the first page of results always works. However, pulling subsequent pages of results gets the following error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Unfortunately, I need to apply the filtering, which requires the joins. Unfortunately I need to load the references and want to avoid a select n+1 situation. I've tried inspecting the SQL expressions in the q object, but it doesn't look like there's any way to see what the true final SQL is that's sent to the server.
Any suggestions?
NOTE: I'm not using AutoQuery, I just appropriated the QueryBase<> and QueryResponse<> objects to use the paging and count properties.
