How would you model and query hierarchical product categories efficiently in SQL and Entity Framework Core?

For shallow or frequently changing trees, an adjacency list (Id, ParentId) is simple. Query descendants with a recursive CTE and prevent cycles. Other models such as materialized paths or closure tables trade more complex writes for faster subtree reads. Example: WITH tree AS ( SELECT Id, ParentId, Name FROM Category WHERE RestaurantId=@RestaurantId AND OutletId=@OutletId AND Id=@RootId UNION ALL SELECT c.Id,c.ParentId,c.Name FROM Category c JOIN tree p ON c.ParentId=p.Id WHERE c.RestaurantId=@RestaurantId AND c.OutletId=@OutletId ) SELECT * FROM tree; Add tenant-qualified indexes, a maximum depth, cycle validation, and deterministic sibling ordering. EF Core may execute the CTE via a mapped SQL query.

Backbencher Developer · 2026-09-26 · 0 likes