Select category tree in Entity Framework

I have a Category table with a tree structure (Id,MasterId) I'd like to select all products that belong to a Category and all Child Categories.

Today I use this SQL Query which works, but I'd like to add pagination and that would be easier with a pure LINQ query. I use 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 how to get a nice LINQ query on this with pagination (current page, number of products per page, to开发者_运维技巧tal product count)?

This is recursive / hiearchical query with table expression. EF does not provide support for such queries. If you want to receive data by single roundtrip to DB you must wrap it in stored procedure and import that procedure to your entity framework model.

Paging in SQL is also possible when using table expressions and ROW_NUMBER().

there is an idea. i haven't tested it, so dont blame if it doesn't work :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));