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 PermissionGroupDefinitions and PermissionDefinitions (remove this redundant Entity suffix), which will populate all navigation properties. — Gert Arnold
– Gert Arnold, Commented Jul 11, 2021 at 9:06
So, I must be use recursive querying? Thanks for you support. — Minh Mít
– Minh Mít, Commented Jul 11, 2021 at 9: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
– Minh Mít, Commented Jul 11, 2021 at 9:59
stackoverflow.com/a/41837737/861716. Removing the suffix is just an advice, it's useless and it reduces readability. — Gert Arnold
– Gert Arnold, Commented Jul 11, 2021 at 11:13
Thanks for your time. I think i'll use recursive querying for this problem. — Minh Mít
– Minh Mít, Commented Jul 11, 2021 at 13:47

marc_s · Accepted Answer · 2021-10-23 15:30:59Z

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.

marc_s · Accepted Answer · 2021-07-21 05:47:46Z

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, 2021 at 5:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 11, 2021 at 2:08

PancakeAlchemist

1222 silver badges10 bronze badges

3 Comments

Minh Mít Over a year ago

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.

PancakeAlchemist Over a year ago

Oh I think I misread the question, I've edited my answer so can you try and see if that fixes your issue.

Minh Mít Over a year ago

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.

Collectives™ on Stack Overflow

Nested include Entity Framework Core

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related