# Gerarchie dei dati: Gestione dei dati gerarchici con EF Core e PostgreSQL (Panoramica) ## Introduzione I dati gerarchici sono ovunque nello sviluppo del software: commenti filettati, grafici organizzativi, file system, categorie di prodotti e discussioni di forum. L'eterna domanda di "come faccio a memorizzare un albero in un database relazionale?" ha perseguitato gli sviluppatori fin dai primi giorni di SQL, e francamente, non c'è ancora nessuna risposta che renda tutti felici. [per la prima volta ha scritto su questo argomento nel 2004](/blog/1188), e le sfide fondamentali rimangono le stesse oggi. ### Perché le gerarchie sono difficili in SQL Ecco il problema fondamentale: **database relazionali pensare in set, non alberi**. Come spiega Joe Celko nel suo eccellente libro [Pensare in set](https://www.amazon.com/Joe-Celkos-Thinking-Sets-Management/dp/0123741378), SQL opera su tabelle intere, non su singole righe. Quando si scrive una query SQL, il motore del database funziona su *insiemi di righe*. È brillante in operazioni come "trovare tutti gli ordini oltre 100" o "unire i clienti ai loro acquisti" - queste sono operazioni impostate che si adattano naturalmente a come funzionano le tabelle. Il risultato è sempre un insieme piatto di righe. Ma una gerarchia è intrinsecamente *ricorsivo*. Per trovare tutti i discendenti di un nodo, è necessario: 1. Trova i bambini più vicini 2. Per ogni bambino, trovare *loro* bambini 3. Ripeti finché non avrai attraversato l'intero sottoalbero. Questa traversata ricorsiva non mappa naturalmente per impostare le operazioni. Non puoi esprimere "dammi tutti i discendenti a qualsiasi profondità" in un'unica, semplice istruzione SQL senza: - [CTE ricorsive](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE) (aggiunto a SQL:1999, ma computazionalmente costoso) - Gite multiple al database - Denormalizzazione intelligente che precomputa le relazioni Ogni approccio in questa serie rappresenta un diverso compromesso tra la complessità di scrittura, la complessità di lettura, e lo stoccaggio in alto. Non c'è nessun pranzo gratuito - si sta sempre scambiando un costo per un altro. Per il riferimento definitivo su questo argomento, vedere Joe Celko [Alberi e gerarchie in SQL per Smarties](https://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/0123877334), che copre tutti questi approcci in profondità. [TOC] ## L'esempio: Commenti di discussione Per fare i confronti concreti, useremo i commenti filettati come esempio di esecuzione - qualcosa che questo stesso blog utilizza. Un commento thread potrebbe assomigliare a questo: ```mermaid flowchart TD subgraph Post["Post: How to Deploy Docker Containers"] C1["Comment 1: Great article!
depth 0"] C2["Comment 2: Thanks!
depth 1"] C3["Comment 3: Very helpful indeed
depth 1"] C4["Comment 4: Agreed!
depth 2"] C5["Comment 5: What about Kubernetes?
depth 0"] C6["Comment 6: That's covered in part 2
depth 1"] end C1 --> C2 C1 --> C3 C3 --> C4 C5 --> C6 style Post stroke:#10b981,stroke-width:2px style C1 stroke:#6366f1,stroke-width:2px style C2 stroke:#8b5cf6,stroke-width:2px style C3 stroke:#8b5cf6,stroke-width:2px style C4 stroke:#a855f7,stroke-width:2px style C5 stroke:#6366f1,stroke-width:2px style C6 stroke:#8b5cf6,stroke-width:2px ``` Dobbiamo sostenere queste operazioni: - **Ottieni tutti i commenti per un post** (con struttura di filettatura conservata) - **Ottenere tutti gli antenati** di un commento (sentiero breadcrumb) - **Ottenere tutti i discendenti** di un commento (intero sottoalbero) - **Aggiungi un nuovo commento** (in risposta a un'osservazione esistente) - **Elimina un sottoalbero** (togli un commento e tutte le risposte) - **Sposta un sottoalbero** (apparentemente un commento - più raro, ma a volte necessario) ## I cinque approcci Ogni approccio è coperto in dettaglio nel suo articolo. Ecco un riassunto per aiutarvi a scegliere: --- ### 1. Elenco di adiacenza (riferimento al genitore) **[Leggi l'articolo completo](/blog/efcore-hierarchical-data-adjacency)** L'approccio più semplice e intuitivo. Ogni riga memorizza un riferimento al suo nodo genitore. E 'quello che la maggior parte degli sviluppatori raggiungono per primo perché mappa naturalmente a come pensiamo alle gerarchie. **Come funziona:** Ogni commento ha un valore nullo `ParentCommentId` colonna. I commenti di radice hanno NULL, le risposte puntano al loro genitore. **Meglio per:** Gerarchie poco profonde (meno di 5-6 livelli), frequenti mosse di sotto-albero, quando si vuole che le proprietà di navigazione dell'EF Core funzionino naturalmente. **Compensazioni:** Ottenere antenati o discendenti richiede CTE ricorsive o domande multiple. Semplice da scrivere, potenzialmente lento a leggere alberi profondi. --- ### 2. Tabella di chiusura **[Leggi l'articolo completo](/blog/efcore-hierarchical-data-closure)** Precomputa e memorizza ogni relazione antenato-discendente in una tabella separata. Invece di capire le relazioni al momento della query, le memorizziamo esplicitamente con la loro profondità. **Come funziona:** Un'altra `CommentClosure` la tabella memorizza (antenator_id, discendente_id, profondità) per ogni coppia. Commento 4 in Commento 1 via Commento 3 avrebbe voci: (1,4,42), (3,41,0), (4,40. **Meglio per:** Applicazioni leggere pesanti, quando è necessario interrogare a profondità arbitrarie, quando raramente si spostano sotto alberi. **Compensazioni:** Inserti più complessi (deve aggiungere elementi di chiusura), storage cresce con profondità, subtrees in movimento richiede la ricostruzione di chiusure. --- ### 3. Percorso materializzato **[Leggi l'articolo completo](/blog/efcore-hierarchical-data-path)** Memorizza l'ascendenza completa come una stringa delimitata. Considerala come una memorizzazione dell'indirizzo postale completo piuttosto che solo il nome della strada. **Come funziona:** Ogni commento ha un `Path` colonna come "1/3/7" che significa "root is 1, genitore è 3, questo è 7." Gli antenati possono essere analizzati dalla stringa; discendenti trovati con query LIKE. **Meglio per:** Generazione Breadcrumb, quando gli antenati sono interrogati più che discendenti, quando si desidera percorsi di debug leggibili dall'uomo. **Compensazioni:** LIKE query può essere lento senza indici adeguati, stringhe di percorso hanno limiti di lunghezza, subtrees in movimento richiede l'aggiornamento di tutti i percorsi discendenti. --- ### 4. Set di Nested **[Leggi l'articolo completo](/blog/efcore-hierarchical-data-nested)** Assegna i numeri di contorno destro e sinistro da una traversata di profondità. Tutti i discendenti hanno valori *tra* I limiti del genitore. **Come funziona:** Ogni commento ha `Left` e `Right` valori. Un nodo con L=4, R=7 contiene tutti i nodi dove 4 < Sinistra e Destra < 7. I discendenti si trovano con semplici query di intervallo. **Meglio per:** Read-heavy, write-rarely scenari come gli alberi di categoria. Eccellente per "get intero sottoalbero in ordine di visualizzazione" query. **Compensazioni:** Gli inserti richiedono l'aggiornamento di molte righe (spostare tutti i valori per fare spazio), spostare i sottoalberi è complesso. --- ### 5. PostgreSQL ltree **[Leggi l'articolo completo](/blog/efcore-hierarchical-data-ltree)** L'estensione nativa di PostgreSQL per i dati gerarchici. Come percorsi materializzati ma con ottimizzazione a livello di database e potente corrispondenza dei pattern. **Come funziona:** Usa il `ltree` tipo di dati con percorsi come "1.3.7." Gli indici GiST consentono richieste di antenati/discendenti efficienti utilizzando operatori come `@>` e `<@`. **Meglio per:** Implementazioni solo PostgreSQL dove le prestazioni sono importanti, quando si hanno bisogno di query corrispondenti ai pattern, quando si desidera il meglio dei percorsi materializzati. **Compensazioni:** Solo PostgreSQL, aggiunge dipendenza dall'estensione del database. Nota: [Npgsql provider supporta le traduzioni LINQ](https://www.npgsql.org/efcore/mapping/translations.html#ltree-functions) per l'albero attraverso il `LTree` tipo, anche se le CTE ricorsive richiedono ancora SQL grezzo. --- ## Sintesi del confronto | Avvicinamento | Inserto | Sottoalbero di domanda | Antenati di query | Sposta sottoalbero | Stoccaggio | Supporto del nucleo dell'impronta ambientale | |----------|--------|---------------|-----------------|--------------|---------|-----------------| | **Elenco di Adjacency** | O(1) | O(n) con CTE | O(d) con CTE | O(1) | Minimale | Eccellente | | **Tabella di chiusura** | O(d) | O(1) | O(s x d) | O(n x d) | Good | | **Percorso materializzato** | O(1) | O(1)* | O(1) | O(s) | O(d) per nodo | Buono | | **Set nidificati** | O(n) | O(1) | O(n) | O(n) | Minimal | Good | | **ltreeCity name (optional, probably does not need a translation)** | O(1) | O(1) | O(s) | O(d) per nodo | Buono (via `LTree` tipo) | *n = nodi totali, d = profondità, s = dimensione sottoalbero* *Con indice corretto ## Schema di flusso decisionale ```mermaid flowchart TD Start([Start]) --> Q1{Shallow tree?
< 5 levels} Q1 -->|Yes| Q2{Need EF Core
navigation properties?} Q2 -->|Yes| AL[Adjacency List] Q2 -->|No| Q3{Performance
critical?} Q3 -->|No| AL Q3 -->|Yes| MP[Materialised Path] Q1 -->|No| Q4{Read-heavy?} Q4 -->|Yes| Q5{Writes rare?} Q5 -->|Yes| NS[Nested Sets] Q5 -->|No| CT[Closure Table] Q4 -->|No| Q6{PostgreSQL only?} Q6 -->|Yes| LT[ltree] Q6 -->|No| Q7{Need breadcrumbs?} Q7 -->|Yes| MP Q7 -->|No| CT style Start stroke:#10b981,stroke-width:2px style AL stroke:#6366f1,stroke-width:2px style MP stroke:#8b5cf6,stroke-width:2px style NS stroke:#ec4899,stroke-width:2px style CT stroke:#f59e0b,stroke-width:2px style LT stroke:#14b8a6,stroke-width:2px ``` ## Scelta del mondo reale: questo blog Questo blog utilizza un **Tabella di chiusura** l'approccio per i commenti. La logica: 1. I commenti sono letti molto più spesso che scritti 2. Dobbiamo visualizzare i thread dei commenti nidificati in modo efficiente 3. Le query che limitano la profondità sono importanti per le prestazioni (riduciamo a 5 livelli di profondità) 4. Commenti in movimento è raro (di tanto in tanto i moderatori hanno bisogno di fare questo) Vedi [Parte 1.2: Tabella di chiusura](/blog/efcore-hierarchical-data-closure) per i dettagli di attuazione completi. ## Navigazione serie - **Parte 1: Panoramica** (questo articolo) - [Parte 1.1: Elenco degli adiacenze](/blog/efcore-hierarchical-data-adjacency) - Semplici riferimenti genitoriali - [Parte 1.2: Tabella di chiusura](/blog/efcore-hierarchical-data-closure) - Relazioni precomputate - [Parte 1.3: Percorso materializzato](/blog/efcore-hierarchical-data-path) - Stringe di tracciato - [Parte 1.4: Set nidificati](/blog/efcore-hierarchical-data-nested) - Confini sinistra/destra - [Parte 1.5: Albero](/blog/efcore-hierarchical-data-ltree) - Estensione nativa PostgreSQL - Parte 2: Raw SQL e Dapper (presto in arrivo)