Most efficient method of self referencing tree using Entity Framework

user_883141 0
Viewed 56064

So I have a SQL table which is basically 

ID, ParentID, MenuName, [Lineage, Depth]

The last two columns are auto-computed to help with searching so we can ignore them for now.

I'm creating a drop down menu system with multiple categories.

Unfortunately EF I don't think plays nice with Self referencing tables more than 1 level deep. So I'm left with a few options

1) Create query, order by depth and then create a custom class in C#, populating it one depth at a time.

2) Find some way to eager load the data in EF, I don't think it is possible for an unlimited amount of levels, only a fixed amount.

3) Some other way I'm not even sure about.

Any inputs would be welcomed!

entity-framework-4 asp.net-mvc c#
6 Answers

@danludwig thanks for your answer

I write some function for update Node, It work's perfect. My code is it good or I should write it in other way?

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