Самый эффективный метод дерева привязки с использованием Entity Framework

Ответ 1

Я успешно сопоставлял иерархические данные с помощью EF.

Возьмем, например, объект Establishment. Это может представлять компанию, университет или другое подразделение в рамках более крупной организационной структуры:

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

Вот как отображаются свойства родителя/детей. Таким образом, когда вы устанавливаете родительский элемент из 1 объекта, родительская сущность Детская коллекция автоматически обновляется:

// 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

Обратите внимание, что до сих пор я не включил свойства Lineage или Depth. Вы правы, EF не работает хорошо для генерации вложенных иерархических запросов с указанными выше отношениями. Наконец, я остановился на добавлении нового объекта gerund вместе с двумя новыми свойствами сущностей:

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<EstablishmentNode> Ancestors { get; set; }
    public virtual ICollection<EstablishmentNode> Offspring { get; set; }

}

При написании этого сообщения hazzik отправил ответ, очень похожий на этот подход. Однако я продолжу писать, чтобы предложить немного другую альтернативу. Мне нравится, чтобы мои предки и потомки gerund отображали фактические типы сущностей, потому что это помогло мне получить Разделение между Предком и Отпрыском (то, что вы назвали Глубиной). Вот как я отобразил их:

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

... и, наконец, идентифицирующие отношения в объекте "Учреждение":

// 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);

Кроме того, я не использовал sproc для обновления сопоставлений node. Вместо этого у нас есть набор внутренних команд, которые будут выводить/вычислять свойства Ancestors and Offspring на основе свойств Parent и Children. Однако, в конечном счете, вы в конечном итоге можете сделать очень похожие запросы, как в ответе хазика:

// 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);

Причиной для объекта моста между основным объектом и его предками/потомками является снова, потому что этот объект позволяет вам получить Разделение. Кроме того, объявляя это как идентифицирующее отношение, вы можете удалить узлы из коллекции без явного вызова DbContext.Delete() на них.

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

Ответ 2

Вы можете использовать поддерживающую таблицу иерархии, чтобы выполнять загрузку неограниченных уровней дерева.

Итак, вам нужно добавить две коллекции Ancestors и Descendants, обе коллекции должны быть сопоставлены как многие-ко-многим для поддержки таблицы.

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

Предки будут содержать всех предков (родителя, внука, внука и т.д.) сущности и Descendants будут содержать всех потомков (детей, внуков, внуков и т.д.) ) объекта.

Теперь вам нужно сопоставить его с EF Code First:

public class TreeConfiguration : EntityTypeConfiguration<Tree>
{
    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"));
    }    
}

Теперь с этой структурой вы можете сделать желаемую выборку, например следующую

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

Этот запрос будет загружать объект с помощью id и все его descenadnts.

Вы можете заполнить таблицу поддержки следующей хранимой процедурой:

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

Или даже вы можете сопоставить таблицу поддержки с представлением:

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

Ответ 3

Я уже потратил некоторое время, пытаясь исправить ошибку в вашем решении. Хранимая процедура действительно не порождает детей, внуков и т.д. Ниже вы найдете исправленную хранимую процедуру:

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

Ошибка: неверная ссылка. Перевод кода @hazzik был следующим:

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

но должен быть

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

Также я добавил код, который позволяет вам обновлять таблицу TreeHierarchy не только при ее заполнении.

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

И волшебство. Эта процедура или, скорее, TreeHierarchy позволяет загружать детей, просто включив Ancestors (а не детей, а не потомков).

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

Теперь YourDbContext вернет rootNode с загруженными детьми, дочерними элементами дочерних элементов rootName (внуки) и т.д.

Ответ 4

Я знал, что в этом решении должно быть что-то не так. Это не просто. Используя это решение, EF6 требует другого пакета хакеров для управления простым деревом (fe. Deletions). Поэтому, наконец, я нашел простое решение, но в сочетании с этим подходом.

Прежде всего оставьте объект простым: достаточно родителя и списка детей. Также отображение должно быть простым:

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

 HasMany(x => x.Children)
    .WithOptional(x => x.Parent);

Затем добавьте перенос (сначала код: миграция: консоль пакета: иерархия добавления-миграции) или другими способами хранимая процедура:

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

Затем, когда вы попытаетесь получить свои узлы дерева из базы данных, просто выполните это в два этапа:

//Get children IDs
var sql = $"EXEC Tree_GetChildren {rootNodeId}";
var children = context.Database.SqlQuery<int>(sql).ToList<int>();

//Get root node and all it 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);

Все. Этот запрос поможет вам получить root node и загрузить всех детей. Не играя с представлением предков и потомков.

Помните также, когда вы попытаетесь сохранить sub node, тогда сделайте это именно так:

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

Сделайте это так, а не добавив детей в root node.

Ответ 5

Другой вариант реализации, который я недавно работал...

Мое дерево очень простое.

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<Node> 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<Node> Nodes { get; set; }
}

Мои требования, не так много.

Учитывая набор листьев и одного предка, покажите детям этого предка, у которых есть потомки, у которых есть листья в пределах набора

Аналогия будет файловой структурой на диске. Текущий пользователь имеет доступ к подмножеству файлов в системе. Когда пользователь открывает узлы в дереве файловой системы, мы хотим показать только узлы пользователей, которые в конечном итоге приведут их к файлам, которые они могут видеть. Мы не хотим показывать им пути к файлам, к которым у них нет доступа (по соображениям безопасности, например, утечка существования документа определенного типа).

Мы хотим выразить этот фильтр как IQueryable<T>, поэтому мы можем применить его к любому запросу node, отфильтровывая нежелательные результаты.

Для этого я создал функцию таблицы, которая возвращает потомки для node в дереве. Он делает это через 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
)

Теперь я использую Code First, поэтому мне пришлось использовать

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

чтобы добавить функцию в мой DbContext

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

со сложным возвращаемым типом (не может повторно использовать Node, глядя на это)

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

Объединяя все это, я разрешил мне, когда пользователь расширяет node в дереве, чтобы получить отфильтрованный список дочерних узлов.

public static Node[] GetVisibleDescendants(int parentId)
{
    using (var db = new Models.Database())
    {
        int[] visibleLeaves = SuperSecretResourceManager.GetLeavesForCurrentUserLol();

        var targetQuery = db.Nodes as IQueryable<Node>;

        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();
    }
}

Важно отметить, что функция выполняется на сервере, а не в приложении. Здесь запрос, который запускается

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
        )
    ))

Обратите внимание на вызов функции в запросе выше.

Ответ 6

@danludwig спасибо за ваш ответ

Я пишу какую-то функцию для обновления Node, она работает отлично. Мой код хорош, или я должен написать его другим способом?

    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<CategoryNode>();
        if (null == ancestorCategory.Ancestors)
        {
            ancestorCategory.Ancestors = new List<CategoryNode>();
        }

        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;
    }