EF Classic - ToSelfHierarchyList - Real Life Scenarios - Include boss, and filter them (EF Core)

CSharp · Console

/Program.cs

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