Select category tree in Entity Framework

Question

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, total product count)?

score 1 · Answer 1 · answered Jan 13 '11 at 17:18

1

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().

answered Jan 13 '11 at 17:18

Ladislav Mrnka

360,892
59
660
670

Is there a way to achieve this with Expressions? Something like [this](http://stackoverflow.com/a/15636530/75500)? – Shimmy Weitzhandler Sep 26 '15 at 19:49

score 0 · Answer 2 · answered May 31 '12 at 06:10

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));

That will require multiple round-trips to the server. – Shimmy Weitzhandler Sep 26 '15 at 19:42 — Shimmy Weitzhandler, Sep 26 '15 at 19:42

Select category tree in Entity Framework

2 Answers2