// Entity Framework Classic

// Doc: https://entityframework-classic.net/to-self-hierarchy-list

​

// @nuget: Z.EntityFramework.Classic

​

using System;

using System.Collections.Generic;

using System.Data.Entity;

using System.Linq;

​

public class Program

{

    public static void Main()

    {

        SeedEmployee();

        using (var context = new EntityContext())

        {

            // The "Boss_2" will not be retrieved

            var employees = context.Employees.Where(x => x.Name.StartsWith("Employee_"))

                .ToSelfHierarchyList(x => x.Boss, options => 

                    options.SelfHierarchyQuery = q => q.Where(x => !x.Name.Contains("2")));

            FiddleHelper.WriteTable("1 - Employee", employees);

            FiddleHelper.WriteTable("2 - Boss", employees.Where(x => x.Boss != null).Select(x => x.Boss).Distinct());

            FiddleHelper.WriteTable("3 - CEO", employees.Where(x => x.Boss != null).Select(x => x.Boss.Boss).Distinct());

        }

    }

    public static void SeedEmployee()

    {

        using (var context = new EntityContext())

        {

            var ceo_1 = new Employee() { Name = "CEO_1" };

            var boss_1 = new Employee() { Name = "Boss_1", Boss = ceo_1 };

            var boss_2 = new Employee() { Name = "Boss_2", Boss = ceo_1 };

            var employee_1 = new Employee() { Name = "Employee_1", Boss = boss_1 };

            var employee_2 = new Employee() { Name = "Employee_2", Boss = boss_2 };

            var employee_3 = new Employee() { Name = "Employee_3", Boss = boss_2 };

​

            context.Employees.AddRange(new List<Employee>() { ceo_1, boss_1, boss_2, employee_1, employee_2, employee_3 });

            context.BatchSaveChanges();

        }

    }

​

    public class EntityContext : DbContext

    {

        public EntityContext() : base(FiddleHelper.GetConnectionStringSqlServer())

        {

​

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            modelBuilder.Entity<Employee>()

                .HasOptional(x => x.Boss)

                .WithMany();

​

            base.OnModelCreating(modelBuilder);

        }

        public DbSet<Employee> Employees { get; set; }

    }

    public class Employee

    {

        public int EmployeeID { get; set; }

        public string Name { get; set; }

​

        public Employee Boss { get; set; }

    }

}