We have a list of projects that may or may not have a collection of subprojects. Our report needs to contain all the projects except those that are the parent project of a subproject.

I need to page this into pages of, say, 25 rows. But if subprojects appear on that page then ALL the subprojects of that project must appear on the same page. So more than 25 items may appear if necessary.

I've got as far as

var pagedProjects = db.Projects.Where(x => !x.SubProjects.Any()).Skip(
    (pageNo -1) * pageSize).Take(pageSize);

Obviously, this fails the second part of the requirements.

As a further pain in the arse, I need to have a pager control on the report. So I'll need to be able to calculate the total number of pages.

I could loop through the whole table of projects but the performance will suffer. Can anybody come up with a paged solution?

EDIT - I should probably mention that SubProjects joins back onto Projects via a selfreferencing foreign key so the whole lot comes back as an IQueryable<Project>.