Map Category Parent Id Self Referencing Table Structure to Ef Core Entity

Map category parent id self referencing table structure to EF Core entity

EF (and LINQ in general) has issues loading tree like data due to lack of recursive expression/CTE support.

But in case you want to load the whole tree (as opposed to filtered tree branch), there is a simple Include based solution. All you need is a single Include and then the EF navigation property fixup will do the work for you. And when you need to get only the root nodes as in your sample, the trick is to apply the filter after the query has been materialized (and navigation properties being fixed) by switching to LINQ to Objects context (using AsEnumerable() as usual).

So the following should produce the desired result with single SQL query:

public override IEnumerable<Category> GetAll()
{ 
    return Table
       .AsEnumerable()
       .Where(x => x.ParentId == null)
       .ToList();
}

Most efficient method of self referencing tree using Entity Framework

I have successfully mapped hierarchical data using EF.

Take for example an Establishment entity. This can represent a company, university, or some other unit within a larger organizational structure:

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

Here is how the Parent / Children properties are mapped. This way, when you set the Parent of 1 entity, the Parent entity's Children collection is automatically updated:

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

Note that so far I haven't included your Lineage or Depth properties. You are right, EF doesn't work well for generating nested hierarchical queries with the above relationships. What I finally settled on was the addition of a new gerund entity, along with 2 new entity properties:

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

}

While writing this up, hazzik posted an answer that is very similar to this approach. I'll continue writing up though, to provide a slightly different alternative. I like to make my Ancestor and Offspring gerund types actual entity types because it helps me get the Separation between the Ancestor and Offspring (what you referred to as Depth). Here is how I mapped these:

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

... and finally, the identifying relationships in the Establishment entity:

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

Also, I did not use a sproc to update the node mappings. Instead we have a set of internal commands that will derive / compute the Ancestors and Offspring properties based on the Parent & Children properties. However ultimately, you end up being able to do some very similar querying as in hazzik's answer:

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

The reason for the bridge entity between the main entity and its Ancestors / Offspring is again because this entity lets you get the Separation. Also, by declaring it as an identifying relationship, you can remove nodes from the collection without having to explicitly call DbContext.Delete() on them.

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

EF Core 2.1: Self-referencing entity with one to many relationship generates additional column

Something's messed up in your migration. No repro when initializing that model:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace EfCoreTest
{

    public class Level
    {
        public int LevelId { get; set; }
        public int? ParentLevelId { get; set; }
        public string Name { get; set; }

        public virtual Level Parent { get; set; }
        public virtual HashSet<Level> Children { get; set; }
    }

    public class Db : DbContext
    {
        public DbSet<Level> levels { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("server=.;database=EfCoreTest;Integrated Security=true");
            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Level>()
                        .HasOne(x => x.Parent)
                        .WithMany(x => x.Children)
                        .HasForeignKey(x => x.ParentLevelId);

        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new Db())
            {
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                Console.ReadKey();

            }

        }
    }
}

creates table:

CREATE TABLE [levels] (
    [LevelId] int NOT NULL IDENTITY,
    [ParentLevelId] int NULL,
    [Name] nvarchar(max) NULL,
    CONSTRAINT [PK_levels] PRIMARY KEY ([LevelId]),
    CONSTRAINT [FK_levels_levels_ParentLevelId] FOREIGN KEY ([ParentLevelId]) REFERENCES [levels] ([LevelId]) ON DELETE NO ACTION
);

Added a Migration,

PM> Add-Migration InitialCreate

and still no repro:

using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;

namespace EfCoreTest.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "levels",
                columns: table => new
                {
                    LevelId = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                    ParentLevelId = table.Column<int>(nullable: true),
                    Name = table.Column<string>(nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_levels", x => x.LevelId);
                    table.ForeignKey(
                        name: "FK_levels_levels_ParentLevelId",
                        column: x => x.ParentLevelId,
                        principalTable: "levels",
                        principalColumn: "LevelId",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_levels_ParentLevelId",
                table: "levels",
                column: "ParentLevelId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "levels");
        }
    }
}

Is there any way I can achieve this in Entity Framework?

If you want to include only top-level entities, and their children listed inside, you could use a Where clause to get only those where ParentVehicleCategory is null:

var test = _raceContext.VehicleCategories
                       .Where(vc => vc.ParentVehicleCategory == null)
                       .Include(x => x.subVehicleCategories)
                       .ToList();

This way, the top-level list is the "parent-less" entities, but the second-level entities will appear in your tree, because those entities are all children of some parent, and is included because of your call to the Include method. If you have only two levels of hierarchy, this method will work for you, but if a "child" entity can also be a "parent", you will need to do some extra work.

Entity Framework Core Parent/Child Clean query with multi level child

If you want limited number of child levels (2 in this example) fetched you can try:

await _context.Offices
    .Where(os => !os.Inactive && !os.ParentId.HasValue)
    .Include(o => o.Parent)
    .Include(o => o.Children)
    .ThenInclude(o => o.Children)
    .ToListAsync()

---

Related Topics