Pagination not working Subsonic

Question

I am having trouble with the pagination. It does not work, i.e. List staffs count is always zero for the code below.

If I remove the Paged method, correct results appear. How else can I solve this? I am using Subsonic 2.2. Pls help - thanks

---

SubSonic.SqlQuery query = DB.Select().Paged(startIndex, pageSize)
             .From<Staff>()
            .InnerJoin(StaffLocation.Schema.TableName, StaffLocation.Columns.StaffId, Staff.Schema.TableName, Staff.Columns.StaffId)
            .InnerJoin(StaffClientGroup.Schema.TableName, StaffClientGroup.Columns.StaffId, Staff.Schema.TableName, Staff.Columns.StaffId)
            .InnerJoin(StaffOutcome.Schema.TableName, StaffOutcome.Columns.StaffId, Staff.Schema.TableName, Staff.Columns.StaffId);
            query.Where("1").IsEqualTo("1");
            if (regionId > 0) query.And(StaffLocation.Columns.RegionId).IsEqualTo(regionId);
            if (clientGroup > 0) query.And(StaffClientGroup.Columns.ClientGroupId).IsEqualTo(clientGroup);
            if (outcome > 0) query.And(StaffOutcome.Columns.OutcomeId).IsEqualTo(outcome);
            query.Distinct();
            query.OrderBys.Add(Staff.Columns.FirstName);
            List<Staff> staffs = query.ExecuteTypedList<Staff>();
            return staffs;

Answer 1

Well, I can tell you that it does work :) and I have a feeling that...

1. Your startIndex is wrong - try using 1 or 2
2. Your pageSize isn't set
3. What is "Where("1").IsEqualTo("1")?

You might try to grab the SQL to see what's being produced...

Answer 2

Put a breakpoint on the second to last line and when it's hit execute query.BuildSqlStatement() in the Visual Studio immediate window and inspect the generated SQL. That might help narrow it down.