facebook

Entity Framework Classic ToSelfHierarchyList

Description

The ToSelfHierarchyList method extends your Entity Framework DbContext to let you easily include a self hierarchy relationship.

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

Try it: NET Core | NET Framework

Real-Life Scenarios

Include boss and add them in the returned list

Like the Include method, by default, the entity from the hierarchy is materialized but not part of the returned list.

The option FlatListRecursionLevel let you include boss in returned list. Use Int.MaxValue to return all levels.

  • FlatListRecursionLevel = 0: will return employee only.
  • FlatListRecursionLevel = 1: will return employee with direct boss.
  • FlatListRecursionLevel = 2: will return employee with direct boss and their boss.
var employees = context.Employees.Where(x => x.Name.StartsWith("Employee_"))
    .ToSelfHierarchyList(x => x.Boss, 
        options => options.FlatListRecursionLevel = 1);

Try it: NET Core | NET Framework

Include boss and filter them

The employee can be filtered as you normally do within the Entity Framework.

The SelfHierarchyQuery option lets you filter the query that retrieves the boss.

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

Try it: NET Core | NET Framework

Include boss, but only direct one

By default, the library makes 10 recursions when retrieving the boss hierarchy.

The MaxRecursion option lets you limit the number of recursions. By specifying a MaxRecursion = 1, you only retrieve the direct boss of your employee.

// The CEO will not be retrieved
var employees = context.Employees.Where(x => x.Name.StartsWith("Employee_"))
    .ToSelfHierarchyList(x => x.Boss, options => 
        options.MaxRecursion = 1);

Try it: NET Core | NET Framework

Include boss, but with custom mapping

If your entity doesn't have navigation property toward boss or employee, it's impossible to use the join expression.

The ColumnMappings option lets you specify the mapping. Careful, the column name and not the property name must be used.

var employees = context.Employees.Where(x => x.Name.StartsWith("Employee_"))
    .ToSelfHierarchyList(null, options => {
        options.ColumnMappings.Add("BossID", "EmployeeID");
        options.FlatListRecursionLevel = int.MaxValue;
    });

Try it: NET Core | NET Framework

Include boss, but with an inverse navigation

If your entity has only a reference to a list of employees and no navigation property towards the boss, it's impossible to use the JoinExpression to include the boss.

The InverseMapping option let you specify a join expression toward employee but to inverse it. So, instead of retrieving their employees, you will retrieve the boss.

var employees = context.Employees.Where(x => x.Name.StartsWith("Employee_"))
    .ToSelfHierarchyList(x => x.Employees, options => {
        options.InverseMapping = true;
        options.FlatListRecursionLevel = int.MaxValue;
    });

Try it: NET Core | NET Framework

Documentation

ToSelfHierarchyList

Methods
Name Description Example
ToSelfHierarchyList(Expression<Func<T, object>> joinExpression) Materialize a list of entities and include the self hierarchy. NET Core / NET Framework
ToSelfHierarchyList(Expression<Func<T, object>> joinExpression, Action<SelfHierarchyListOptions<T>> options) Materialize a list of entities and include the self hierarchy. NET Core / NET Framework
Options
Name Description Example
ColumnMappings Gets or sets the column mappings. NET Core / NET Framework
FlatListRecursionLevel Gets or sets the flat list recursion level. Default = 0 which return only item from the query. NET Core / NET Framework
InverseMapping Gets or sets a value indicating whether the mapping is inversed. NET Core / NET Framework
JoinExpression Gets or sets the join expression. NET Core / NET Framework
MaxRecursion Gets or sets the maximum recursion to perform. Default = 10. NET Core / NET Framework
SelfHierarchyQuery Gets or sets the filtered self hierarchy query to use. NET Core / NET Framework

Limitations

  • Support SQL Server only