Hello, I have a problem. There is a category table that looks like this: Category table

The task when choosing a category of higher hierarchy is to pull up all its subcategories - to create a collection of tree categories. The question is: how to implement such a task using the Entity Framework? Is it possible to do this with a single query to the database?

For any help I would be grateful)

  • join? [15 characters] - VladD 1:01 pm

3 answers 3

Entity Framework does not know how to recursive queries. But the problem can be solved.

Option 1:

Make a view based on a recursive SQL query using WITH CTE . Then in the code to cause data retrieval from this twist.

Sample SQL code:

 CREATE VIEW [dbo].[vw_Subcategories] AS WITH cte ( CategoryID, SubcatID) AS ( SELECT Id AS SubcatID, RootId as CategoryID FROM dbo.TableName UNION ALL SELECT e.Id AS SubcatID, cte.Id AS CategoryID FROM cte INNER JOIN dbo.TableName AS e ON e.RootId = cte.Id )

Option 2:

To make a simple separate recursive method in the code for getting child EF-objects:

 List<Category> GoDownRecursive(int categoryId) { var res = new List<Category>(); foreach(var subcategory in DbContext.Cateogires.Where(c => c.RootId == categoryId) { res.Add(subcategory); res.AddRange(GoDownRecursive(subcategory.Id)); } return res; }

There will be more requests to the database, but if the nesting is small and the request is not the most frequent, then the option is acceptable. If desired, the resulting trees can be cached.

  • If you use option 1, it gives an error message: "Incorrect syntax around the construction") ".". How to fix it, do not tell me? - unitreid
  • @unitreid corrected the request. Note that the query returns only the first level of subcategories. To return the result from the view, you need to compare categoryId with the Id of the selected category. - Vladimir Zhukov

Entity works great with SQL-stored procedures - try them!

Pros: Potential performance gains because all intermediate samples occur on the SQL side.

Cons: Additional "spraying" of business logic.

    Well, why not do this: in an entity for a category, make a list of subcategories, and disable lazy loading for them?

    It describes how to disable lazy loading, both completely and for specific properties or in general only for one operation.

    Most likely, you just fit

     var categories = context.Categories.Include("SubCategories").ToList();
    • The list of subcategories will be difficult to keep up to date. I would like a more dynamic option. - unitreid
    • Uh ... Why is it difficult? What flow do you see the problem in? - VladD