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?

You can't, at least not in one LINQ statement. This is a tree expansion of unknown depth, which requires recursive querying. Or if you want the whole tree, just query all `PermissionGroupDefinition`s and `PermissionDefinition`s (remove this redundant `Entity` suffix), which will populate all navigation properties. — Gert Arnold, Jul 11 '21 at 09:06
So, I must be use recursive querying? Thanks for you support. — Minh Mít, Jul 11 '21 at 09:55
But can you explain about how can i get the whole tree? I don't understand why remove Entity suffix? I think it just class name. @gert-arnold — Minh Mít, Jul 11 '21 at 09:59
https://stackoverflow.com/a/41837737/861716. Removing the suffix is just an advice, it's useless and it reduces readability. — Gert Arnold, Jul 11 '21 at 11:13
Thanks for your time. I think i'll use recursive querying for this problem. — Minh Mít, Jul 11 '21 at 13:47

score 3 · Accepted Answer · edited Oct 23 '21 at 15:30

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.

score 0 · Answer 2 · edited Jul 21 '21 at 05:47

0

You can use .ThenInclude(i => ...) like so

var query = _context.PermissionGroupDefinitionEntity
  .AsNoTracking()
  .Include(i => i.PermissionDefinitions)
      .ThenInclude(i => i.Group)
  .AsQueryable();

Edit:

var query = _context.PermissionGroupDefinitionEntity
  .AsNoTracking()
  .Include(i => i.PermissionDefinitions)
      .ThenInclude(i => i.Children)
  .AsQueryable();

edited Jul 21 '21 at 05:47

marc_s

732,580
175
1,330
1,459

answered Jul 11 '21 at 02:08

PancakeAlchemist

112
2
10

Thanks for your support. But when i use this query. I received error: System.InvalidOperationException: 'The Include path 'PermissionDefinitions->Group' results in a cycle. Cycles are not allowed in no-tracking queries; either use a tracking query or remove the cycle.'. And when i remove AsNoTracking. This query return only first level of PermissionDefinitions. – Minh Mít Jul 11 '21 at 02:38
1

Oh I think I misread the question, I've edited my answer so can you try and see if that fixes your issue. – PancakeAlchemist Jul 11 '21 at 08:08
Thank for your support. This query return maximum two-level of PermissionDefinition. As PermissionGroupDefinition => PermissionDefinition => PermissionDefinition. If i have struct PermissionGroupDefinition => PermissionDefinition => PermissionDefinition => PermissionDefinition it not working. – Minh Mít Jul 11 '21 at 09:38

Nested include Entity Framework Core

2 Answers2