Nested include Entity Framework Core

Question

I'm using EF Core for my project. And I have a problem with nested query in EF Core.

I have 2 classes:

public class PermissionGroupDefinitionEntity : IEntity
{
    public string Name { get; set; }
    public string NormalizedName { get; set; }
    public string DisplayName { get; set; }

    public virtual ICollection<PermissionDefinitionEntity> PermissionDefinitions { get; set; }
}

public class PermissionDefinitionEntity : IEntity
{
    public string Name { get; set; }
    public string NormalizedName { get; set; }
    public string DisplayName { get; set; }

    public bool IsEnabled { get; set; }
    public virtual string GroupName { get; set; }

    public virtual PermissionGroupDefinitionEntity Group { get; set; }
    public virtual ICollection<PermissionDefinitionEntity> Children { get; set; }
}

and this is the ApplicationDbContext:

        builder.Entity<PermissionDefinitionEntity>().HasOne(r => r.Group).WithMany(r => r.PermissionDefinitions).OnDelete(DeleteBehavior.Cascade);
        builder.Entity<PermissionDefinitionEntity>().HasOne(r => r.Parent).WithMany(r => r.Children).OnDelete(DeleteBehavior.Cascade);

I want query all PermissionGroupDefinitionEntity included PermissionDefinitionEntity and self referencing of PermissionDefinitionEntity.

Can I do that with EF Core?

Answer 1

You need to recursively load PermissionDefinitions that placed in the PermissionGroupDefinitionEntity.

First, you should load all PermissionGroupDefinitionEntities including its children using the following query :

var query = _dbContext.PermissionGroupDefinitionEntity
            .AsNoTracking()
            .Include(p => p.PermissionDefinitions )
            .ThenInclude(p => p.Children)
            .ToListAsync();

Since every PermissionGroupDefinitionEntity has a list of PermissionDefinition you need a nested loops like this code :

foreach (var PermissionGroupDefinitionEntity in PermissionGroupDefinitionEntities)
{
    foreach (var PermissionDefinitions in PermissionDefinitions)
    {

    }
}

Then in the inner loop you should call your recursive function.

See following link (sample for get all children recursively in Entity Framework Core)

https://patrickdesjardins.com/blog/how-to-load-hierarchical-structure-with-recursive-with-entity-framework-5

This way has terrible performance and I don't recommend that.

In this case it's seems you must write a stored procedure in SQL for better performance.