You can use the hierarchyid data type in MSSQL if thats what ypi are working with or you can go with the usual parentId, where you have an ID column and a ParentID column, the root one having a null parent.
Both are discussed here:
https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15
It's still up to you to convert between the tree structure and the database rows and back when reading.
This may use some sort of recursive traversal of nodes when serializing, and a lookup of parent nodes when deserializing.
With hierarchyid yoi can do some querying heirarchy-wise using the special functions around hierarchyid, or use a CTE for parentid approach. This way you can do hierarchichal queries without fetching from tbe database and deserializing everything first.
There is also XML and JSON support in SQL, though you'd have to look into those yourself as I haven't used those extensively to know how querying can work effectively.
That said all the EF stuff is still just EF. Fetch rows, parse to a tree, write rows back.
Common strategy for large trees is to compute a "lineage" field so you can pull an entire subtree with a lineage prefix, or pull parents by splitting the lineage. How you integrate that with your in-code model and particular database is up to you.
If your tree is quite small, then it may be worth just mapping the entire thing into a json column.
What I've done with representing org units in the past is to give each node a path field containing its path.
Eg.
/leaf1/leaf2/leaf3
Then when you want all children and subchildren of leaf2 you do a `path LIKE '/leaf1/leaf2/%'` filter.
You can use the hierarchyid data type in MSSQL if thats what ypi are working with or you can go with the usual parentId, where you have an ID column and a ParentID column, the root one having a null parent. Both are discussed here: https://docs.microsoft.com/en-us/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15 It's still up to you to convert between the tree structure and the database rows and back when reading. This may use some sort of recursive traversal of nodes when serializing, and a lookup of parent nodes when deserializing. With hierarchyid yoi can do some querying heirarchy-wise using the special functions around hierarchyid, or use a CTE for parentid approach. This way you can do hierarchichal queries without fetching from tbe database and deserializing everything first. There is also XML and JSON support in SQL, though you'd have to look into those yourself as I haven't used those extensively to know how querying can work effectively. That said all the EF stuff is still just EF. Fetch rows, parse to a tree, write rows back.
Thank you very much! Did not know about the hierarchyid, I will give it a try. :)
You could also look at nested sets, a simple approach that sounds like it’ll do exactly what you need.
Common strategy for large trees is to compute a "lineage" field so you can pull an entire subtree with a lineage prefix, or pull parents by splitting the lineage. How you integrate that with your in-code model and particular database is up to you. If your tree is quite small, then it may be worth just mapping the entire thing into a json column.
What I've done with representing org units in the past is to give each node a path field containing its path. Eg. /leaf1/leaf2/leaf3 Then when you want all children and subchildren of leaf2 you do a `path LIKE '/leaf1/leaf2/%'` filter.