Il metodo più efficiente per l’auto autoreferenziale che utilizza Entity Framework

Quindi ho una tabella SQL che è fondamentalmente

ID, ParentID, MenuName, [Lineage, Depth] 

Le ultime due colonne sono auto-calcolate per aiutare con la ricerca in modo che possiamo ignorarle per ora.

Sto creando un sistema di menu a discesa con più categorie.

Sfortunatamente EF non credo che giochi con le tabelle Self referencing più di 1 livello di profondità. Quindi sono rimasto con alcune opzioni

1) Crea query, ordina per profondità e poi crea una class personalizzata in C #, popolandola una profondità alla volta.

2) Trova un modo per caricare i dati in EF, non credo sia ansible una quantità illimitata di livelli, solo una quantità fissa.

3) In un altro modo di cui non sono nemmeno sicuro.

Qualsiasi input sarebbe ben accetto!

Ho mappato correttamente i dati gerarchici usando EF.

Prendi ad esempio un’ quadro Establishment . Questo può rappresentare un’azienda, un’università o qualche altra unità all’interno di una struttura organizzativa più ampia:

 public class Establishment : Entity { public string Name { get; set; } public virtual Establishment Parent { get; set; } public virtual ICollection Children { get; set; } ... } 

Ecco come vengono mappate le proprietà Parent / Children. In questo modo, quando si imposta l’ quadro padre di 1, la raccolta figli dell’entity framework padre viene aggiornata automaticamente:

 // ParentEstablishment 0..1 <---> * ChildEstablishment HasOptional(d => d.Parent) .WithMany(p => p.Children) .Map(d => d.MapKey("ParentId")) .WillCascadeOnDelete(false); // do not delete children when parent is deleted 

Nota che finora non ho incluso le proprietà di Lignaggio o Depth. Hai ragione, EF non funziona bene per generare query gerarchiche annidate con le relazioni di cui sopra. Alla fine ho optato per l’aggiunta di una nuova quadro gerunda e di due nuove proprietà dell’entity framework:

 public class EstablishmentNode : Entity { public int AncestorId { get; set; } public virtual Establishment Ancestor { get; set; } public int OffspringId { get; set; } public virtual Establishment Offspring { get; set; } public int Separation { get; set; } } public class Establishment : Entity { ... public virtual ICollection Ancestors { get; set; } public virtual ICollection Offspring { get; set; } } 

Durante la stesura di questo, hazzik ha pubblicato una risposta molto simile a questo approccio . Continuerò comunque a scrivere, per fornire un’alternativa leggermente diversa. Mi piace rendere i miei tipi di quadro Ancestor e Offspring effettivi tipi di quadro perché mi aiuta a ottenere la Separazione tra l’Antenato e la Progenie (ciò che definisci Profondità). Ecco come ho mappato questi:

 private class EstablishmentNodeOrm : EntityTypeConfiguration { internal EstablishmentNodeOrm() { ToTable(typeof(EstablishmentNode).Name); HasKey(p => new { p.AncestorId, p.OffspringId }); } } 

… e infine, le relazioni identificative nell’entity framework Establishment:

 // has many ancestors HasMany(p => p.Ancestors) .WithRequired(d => d.Offspring) .HasForeignKey(d => d.OffspringId) .WillCascadeOnDelete(false); // has many offspring HasMany(p => p.Offspring) .WithRequired(d => d.Ancestor) .HasForeignKey(d => d.AncestorId) .WillCascadeOnDelete(false); 

Inoltre, non ho usato un sproc per aggiornare i mapping dei nodes. Invece abbiamo una serie di comandi interni che derivano / calcolano le proprietà di Antenati e Prole in base alle proprietà Genitore e Bambino. Tuttavia alla fine, si finisce per essere in grado di fare alcune query molto simili come nella risposta di hazzik:

 // load the entity along with all of its offspring var establishment = dbContext.Establishments .Include(x => x.Offspring.Select(y => e.Offspring)) .SingleOrDefault(x => x.Id == id); 

La ragione dell’ quadro ponte tra l’ quadro principale e i suoi antenati / prole è di nuovo perché questa entity framework ti consente di ottenere la separazione. Inoltre, dichiarandolo come una relazione identificativa, è ansible rimuovere i nodes dalla raccolta senza dover richiamare esplicitamente DbContext.Delete () su di essi.

 // load all entities that are more than 3 levels deep var establishments = dbContext.Establishments .Where(x => x.Ancestors.Any(y => y.Separation > 3)); 

È ansible utilizzare la tabella gerarchica di supporto per eseguire il caricamento impaziente di livelli illimitati di albero.

Quindi, è necessario aggiungere due raccolte Ancestors e Descendants , entrambe le raccolte dovrebbero essere mappate come molti a molti alla tabella di supporto.

 public class Tree { public virtual Tree Parent { get; set; } public virtual ICollection Children { get; set; } public virtual ICollection Ancestors { get; set; } public virtual ICollection Descendants { get; set; } } 

Gli antenati conterranno tutti gli antenati (genitore, nonno, bisnonno, ecc.) Dell’ quadro e i Descendants conterranno tutti i discendenti (bambini, nipoti, bisnonni, ecc.) Dell’ quadro.

Ora devi mapparlo con EF Code First:

 public class TreeConfiguration : EntityTypeConfiguration { public TreeConfiguration() { HasOptional(x => x.Parent) .WithMany(x => x.Children) .Map(m => m.MapKey("PARENT_ID")); HasMany(x => x.Children) .WithOptional(x => x.Parent); HasMany(x => x.Ancestors) .WithMany(x => x.Descendants) .Map(m => m.ToTable("Tree_Hierarchy").MapLeftKey("PARENT_ID").MapRightKey("CHILD_ID")); HasMany(x => x.Descendants) .WithMany(x => x.Ancestors) .Map(m => m.ToTable("Tree_Hierarchy").MapLeftKey("CHILD_ID").MapRightKey("PARENT_ID")); } } 

Ora con questa struttura si potrebbe fare il recupero come seguendo

 context.Trees.Include(x => x.Descendants).Where(x => x.Id == id).SingleOrDefault() 

Questa query caricherà l’ quadro con id e tutti i discenadnts.

È ansible popolare la tabella di supporto con la seguente stored procedure:

 CREATE PROCEDURE [dbo].[FillHierarchy] (@table_name nvarchar(MAX), @hierarchy_name nvarchar(MAX)) AS BEGIN DECLARE @sql nvarchar(MAX), @id_column_name nvarchar(MAX) SET @id_column_name = '[' + @table_name + '_ID]' SET @table_name = '[' + @table_name + ']' SET @hierarchy_name = '[' + @hierarchy_name + ']' SET @sql = '' SET @sql = @sql + 'WITH Hierachy(CHILD_ID, PARENT_ID) AS ( ' SET @sql = @sql + 'SELECT ' + @id_column_name + ', [PARENT_ID] FROM ' + @table_name + ' e ' SET @sql = @sql + 'UNION ALL ' SET @sql = @sql + 'SELECT e.' + @id_column_name + ', e.[PARENT_ID] FROM ' + @table_name + ' e ' SET @sql = @sql + 'INNER JOIN Hierachy eh ON e.' + @id_column_name + ' = eh.[PARENT_ID]) ' SET @sql = @sql + 'INSERT INTO ' + @hierarchy_name + ' ([CHILD_ID], [PARENT_ID]) ( ' SET @sql = @sql + 'SELECT [CHILD_ID], [PARENT_ID] FROM Hierachy WHERE [PARENT_ID] IS NOT NULL ' SET @sql = @sql + ') ' EXECUTE (@sql) END GO 

O persino potresti mappare la tabella di supporto a una vista:

 CREATE VIEW [Tree_Hierarchy] AS WITH Hierachy (CHILD_ID, PARENT_ID) AS ( SELECT [MySuperTree_ID], [PARENT_ID] FROM [MySuperTree] AS e UNION ALL SELECT e.[MySuperTree_ID], e.[PARENT_ID] FROM [MySuperTree] AS e INNER JOIN Hierachy AS eh ON e.[MySuperTree_ID] = eh.[PARENT_ID] ) SELECT [CHILD_ID], [PARENT_ID] FROM Hierachy WHERE [PARENT_ID] IS NOT NULL GO 

Ho già trascorso un po ‘di tempo cercando di correggere un bug nella tua soluzione. La procedura memorizzata in realtà non genera figli, nipoti, ecc. Sotto troverai la stored procedure fissa:

 CREATE PROCEDURE dbo.UpdateHierarchy AS BEGIN DECLARE @sql nvarchar(MAX) SET @sql = '' SET @sql = @sql + 'WITH Hierachy(ChildId, ParentId) AS ( ' SET @sql = @sql + 'SELECT t.Id, t.ParentId FROM dbo.Tree t ' SET @sql = @sql + 'UNION ALL ' SET @sql = @sql + 'SELECT h.ChildId, t.ParentId FROM dbo.Tree t ' SET @sql = @sql + 'INNER JOIN Hierachy h ON t.Id = h.ParentId) ' SET @sql = @sql + 'INSERT INTO dbo.TreeHierarchy (ChildId, ParentId) ( ' SET @sql = @sql + 'SELECT DISTINCT ChildId, ParentId FROM Hierachy WHERE ParentId IS NOT NULL ' SET @sql = @sql + 'EXCEPT SELECT t.ChildId, t.ParentId FROM dbo.TreeHierarchy t ' SET @sql = @sql + ') ' EXECUTE (@sql) END 

Errore: riferimento errato. Traducendo il codice @hazzik era:

  SET @sql = @sql + 'SELECT t.ChildId, t.ParentId FROM dbo.Tree t ' 

ma dovrebbe essere

  SET @sql = @sql + 'SELECT h.ChildId, t.ParentId FROM dbo.Tree t ' 

inoltre ho aggiunto il codice che ti permette di aggiornare la tabella TreeHierarchy non solo quando la popolerai.

  SET @sql = @sql + 'EXCEPT SELECT t.ChildId, t.ParentId FROM dbo.TreeHierarchy t ' 

E la magia. Questa procedura o piuttosto TreeHierarchy consente di caricare i bambini includendo solo antenati (non figli e non discendenti).

  using (var context = new YourDbContext()) { rootNode = context.Tree .Include(x => x.Ancestors) .SingleOrDefault(x => x.Id == id); } 

Ora YourDbContext restituirà un rootNode con i bambini caricati, i figli dei figli di rootName (nipoti) e così via.

Sapevo che ci doveva essere qualcosa di sbagliato in questa soluzione. Non è semplice Utilizzando questa soluzione, EF6 richiede un altro pacchetto di hack per gestire un albero semplice (ad esempio delezioni). Così finalmente ho trovato una soluzione semplice ma unita a questo approccio.

Prima di tutto lascia semplice l’ quadro: solo il genitore e l’elenco dei bambini è sufficiente. Anche la mapping dovrebbe essere semplice:

  HasOptional(x => x.Parent) .WithMany(x => x.Children) .Map(m => m.MapKey("ParentId")); HasMany(x => x.Children) .WithOptional(x => x.Parent); 

Quindi aggiungere la migrazione (prima il codice: migrazioni: console del pacchetto: Aggiungi-Migration Hierarchy) o in altri modi una stored procedure:

 CREATE PROCEDURE [dbo].[Tree_GetChildren] (@Id int) AS BEGIN WITH Hierachy(ChildId, ParentId) AS ( SELECT ts.Id, ts.ParentId FROM med.MedicalTestSteps ts UNION ALL SELECT h.ChildId, ts.ParentId FROM med.MedicalTestSteps ts INNER JOIN Hierachy h ON ts.Id = h.ParentId ) SELECT h.ChildId FROM Hierachy h WHERE h.ParentId = @Id END 

Quindi, quando proverai a ricevere i tuoi nodes dell’albero dal database, fallo in due passaggi:

 //Get children IDs var sql = $"EXEC Tree_GetChildren {rootNodeId}"; var children = context.Database.SqlQuery(sql).ToList(); //Get root node and all it's children var rootNode = _context.TreeNodes .Include(s => s.Children) .Where(s => s.Id == id || children.Any(c => s.Id == c)) .ToList() //MUST - get all children from database then get root .FirstOrDefault(s => s.Id == id); 

Tutto. Questa query ti aiuta a ottenere un nodo radice ea caricare tutti i bambini. Senza giocare con l’introduzione di antenati e discendenti.

Ricorda anche quando proverai a salvare il sub-nodo, quindi fallo in questo modo:

 var node = new Node { ParentId = rootNode }; //Or null, if you want node become a root context.TreeNodess.Add(node); context.SaveChanges(); 

Fatelo in quel modo, non aggiungendo i figli al nodo radice.

Un’altra opzione di implementazione su cui ho lavorato recentemente …

Il mio albero è molto semplice.

 public class Node { public int NodeID { get; set; } public string Name { get; set; } public virtual Node ParentNode { get; set; } public int? ParentNodeID { get; set; } public virtual ICollection ChildNodes { get; set; } public int? LeafID { get; set; } public virtual Leaf Leaf { get; set; } } public class Leaf { public int LeafID { get; set; } public string Name { get; set; } public virtual ICollection Nodes { get; set; } } 

I miei requisiti, non così tanto.

Dato un insieme di foglie e un singolo antenato, mostra ai bambini di quell’antenato che hanno discendenti che hanno foglie all’interno del set

Un’analogia sarebbe una struttura di file su disco. L’utente corrente ha accesso a un sottoinsieme di file sul sistema. Quando l’utente apre i nodes nell’albero del file system, vogliamo solo mostrare quei nodes utente che, alla fine, li porteranno ai file che possono vedere. Non vogliamo mostrare loro i percorsi dei file ai file ai quali non hanno accesso (per motivi di sicurezza, ad esempio, la perdita di un documento di un certo tipo).

Vogliamo essere in grado di esprimere questo filtro come IQueryable , quindi possiamo applicarlo a qualsiasi query del nodo, filtrando i risultati indesiderati.

Per fare questo, ho creato una funzione con valori di tabella che restituisce i discendenti per un nodo nell’albero. Lo fa tramite un CTE.

 CREATE FUNCTION [dbo].[DescendantsOf] ( @parentId int ) RETURNS TABLE AS RETURN ( WITH descendants (NodeID, ParentNodeID, LeafID) AS( SELECT NodeID, ParentNodeID, LeafID from Nodes where ParentNodeID = @parentId UNION ALL SELECT n.NodeID, n.ParentNodeID, n.LeafID from Nodes n inner join descendants d on n.ParentNodeID = d.NodeID ) SELECT * from descendants ) 

Ora, sto usando Code First, quindi ho dovuto usare

https://www.nuget.org/packages/EntityFramework.Functions

per aggiungere la funzione al mio DbContext

 [TableValuedFunction("DescendantsOf", "Database", Schema = "dbo")] public IQueryable DescendantsOf(int parentID) { var param = new ObjectParameter("parentId", parentID); return this.ObjectContext().CreateQuery("[DescendantsOf](@parentId)", param); } 

con un tipo di ritorno complesso (imansible riutilizzare il nodo, esaminandolo)

 [ComplexType] public class NodeDescendant { public int NodeID { get; set; } public int LeafID { get; set; } } 

Mettere tutto insieme mi ha permesso, quando l’utente espande un nodo nell’albero, di ottenere l’elenco filtrato dei nodes figli.

 public static Node[] GetVisibleDescendants(int parentId) { using (var db = new Models.Database()) { int[] visibleLeaves = SuperSecretResourceManager.GetLeavesForCurrentUserLol(); var targetQuery = db.Nodes as IQueryable; targetQuery = targetQuery.Where(node => node.ParentNodeID == parentId && db.DescendantsOf(node.NodeID).Any(x => visibleLeaves.Any(y => x.LeafID == y))); // Notice, still an IQueryable. Perform whatever processing is required. SortByCurrentUsersSavedSettings(targetQuery); return targetQuery.ToArray(); } } 

È importante notare che la funzione viene eseguita sul server, non nell’applicazione . Ecco la query che viene eseguita

 SELECT [Extent1].[NodeID] AS [NodeID], [Extent1].[Name] AS [Name], [Extent1].[ParentNodeID] AS [ParentNodeID], [Extent1].[LeafID] AS [LeafID] FROM [dbo].[Nodes] AS [Extent1] WHERE ([Extent1].[ParentNodeID] = @p__linq__0) AND ( EXISTS (SELECT 1 AS [C1] FROM ( SELECT [Extent2].[LeafID] AS [LeafID] FROM [dbo].[DescendantsOf]([Extent1].[NodeID]) AS [Extent2] ) AS [Project1] WHERE EXISTS (SELECT 1 AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE [Project1].[LeafID] = 17 ) )) 

Annota la chiamata alla funzione all’interno della query sopra.

@ Danludwig grazie per la tua risposta

Scrivo alcune funzioni per il nodo di aggiornamento, funziona perfettamente. Il mio codice è buono o dovrei scriverlo in altro modo?

  public void Handle(ParentChanged e) { var categoryGuid = e.CategoryId.Id; var category = _context.Categories .Include(cat => cat.ParentCategory) .First(cat => cat.Id == categoryGuid); if (null != e.OldParentCategoryId) { var oldParentCategoryGuid = e.OldParentCategoryId.Id; if (category.ParentCategory.Id == oldParentCategoryGuid) { throw new Exception("Old Parent Category mismatch."); } } (_context as DbContext).Configuration.LazyLoadingEnabled = true; RemoveFromAncestors(category, category.ParentCategory); var newParentCategoryGuid = e.NewParentCategoryId.Id; var parentCategory = _context.Categories .First(cat => cat.Id == newParentCategoryGuid); category.ParentCategory = parentCategory; AddToAncestors(category, category.ParentCategory, 1); _context.Commit(); } private static void RemoveFromAncestors(Model.Category.Category mainCategory, Model.Category.Category ancestorCategory) { if (null == ancestorCategory) { return; } while (true) { var offspring = ancestorCategory.Offspring; offspring?.RemoveAll(node => node.OffspringId == mainCategory.Id); if (null != ancestorCategory.ParentCategory) { ancestorCategory = ancestorCategory.ParentCategory; continue; } break; } } private static int AddToAncestors(Model.Category.Category mainCategory, Model.Category.Category ancestorCategory, int deep) { var offspring = ancestorCategory.Offspring ?? new List(); if (null == ancestorCategory.Ancestors) { ancestorCategory.Ancestors = new List(); } var node = new CategoryNode() { Ancestor = ancestorCategory, Offspring = mainCategory }; offspring.Add(node); if (null != ancestorCategory.ParentCategory) { deep = AddToAncestors(mainCategory, ancestorCategory.ParentCategory, deep + 1); } node.Separation = deep; return deep; }