CODEWITHSUNDEEP
c#linqentity-framework
RobbanP
RobbanP

Reputation: 143

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

Upvotes: 3

Views: 1405

Answers (2)

maxlego
maxlego

Reputation: 4914

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

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

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

Upvotes: 1

Related Questions