.NET Data Community Standup - Using hierarchical data in SQL Server and PostgreSQL with EF Core

Join the .NET Data Access Team to learn about mapping hierarchical data structures to relational databases using EF Core. We’ll look at “hierarchyid” on SQL Server/Azure SQL and “ltree” on PostgresSQL, and show how to map tree structures such as a family tree, file system, or organization structure. EF Core can then be used to write LINQ queries against the hierarchy to find ancestors and descendants in various ways, as well as perform manipulation of subtrees for updates. And, as always, we’ll be here to answer your questions!

Chapters:
00:00 Livestream begins
02:40 Welcome to the .NET Data Community Standup
03:27 State of the Unicorn
05:14 Hierarchical data
06:51 SQL Server Tree structure
09:50 SP Server Hierarchy ID History
13:44 Hierarchy ID Packages
17:04 SQL Server Hierarchy ID
17:54 Demo
19:56 PostgreSQL
23:29 Postgres
25:22 Group by
28:03 Find all generations
33:32 Find descendants
37:24 Find descendants in PostgreSQL
39:38 Debugging
42:54 Subpath in PostgreSQL
50:04 Summary
55:24 Example
01:18:25 Wrap


#EntityFramework #CSharp #Data

[justintowers]

ok, so I was running into an issue where when I tried to return an object that contained an LTree type,
"The 'NLevel' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rewrite the query to avoid client evaluation of arguments so that method can be translated to server."

So the workaround if you want to return the path, is to put it in an anonymous object inside a select statement and ToString() it.

[memelet]

Is the sample code available somewhere?

[amrosamy]

How to uniquely and sequentially create a hierarchical id based on a specific parent?
Is it a good solution for hierarchical tenant and organization management?