Select category tree in Entity Framework

Question

I have a category table with a tree structure (Id, MasterId) I would like to select all products that belong to the category and all child categories.

Today I use this SQL Query, which works, but I would like to add pagination, and that would be easier with a pure LINQ query. I am using Entity Framework 4.

@Count int = 100,
@CategoryId int

with mq as
(
    select c.Id as parent, c.Id as child 
    from dbo.Categories c 
    where c.Id = @CategoryId
    union all
    select q.child, c.Id
    from mq q
    inner join dbo.Categories c on q.child = c.MasterId
)

select top (@Count) P.* from Products P
inner join ProductToCategory PC ON(PC.ProductId = P.Id)
where PC.CategoryId in (
    select child from mq
)
and P.PublishStatus = 1
order by P.PublishedDate DESC;

Any ideas on how to get a good LINQ query on this pagination (current page, number of products per page, total number of products)?

Answer 1

/hiearchical . EF . , .

SQL ROW_NUMBER().

Answer 2

. , , : P

    var ids = context.TreeItems.Where(x => x.Id == parentId).Select(x => (int?)x.Id);

    var tmp = ids;
    while (true)
    {
        IQueryable<int?> localIds = tmp;
        var subIds = context.TreeItems.Where(x => ids.Contains(x.ParentId)).Select(x => (int?)x.Id);
    if (subIds.Any())
    {
        tmp = subIds;
        ids = ids.Union(subIds);
            }
    else
        break;
}

    var allSubItems = context.TreeItems.Where(x => ids.Contains(x.Id));