Entity Framework Core Parent/Child Clean query with multi level child

Question

I have an issue on cleaning querying my parent child without repeating the children at its own root of the list.

Object

 public class Office
    {

        public int Id { get; set; }
        [Required]
        public string Name { get; set; }
        public bool Inactive { get; set; }
        public int? ParentId { get; set; }
        [ForeignKey("ParentId")]
        public Office Parent { get; set; }
        [InverseProperty("Parent")]
        public virtual ICollection<Office> Children { get; set; }
    }
}

The Query

public async Task<IEnumerable<Office>> GetOffices()
        {
            return await _context.Offices.Where(os => !os.Inactive)
                   .Include(o => o.Parent)
                   .Include(o => o.Children).ToListAsync();
        }

And the result

 {
        "id": 5,
        "name": "AA",
        "inactive": false,
        "parentId": null,
        "parent": null,
        "children": []
    },
    {
        "id": 10,
        "name": "BAA",
        "inactive": false,
        "parentId": 2,
        "parent": {
            "id": 2,
            "name": "BA",
            "inactive": false,
            "parentId": null,
            "parent": null,
            "children": []
        },
        "children": [
            {
                "id": 1011,
                "name": "BAAA",
                "inactive": false,
                "parentId": 10,
                "children": []
            }
        ]
    },
    {
        "id": 1011,
        "name": "BAAA",
        "inactive": false,
        "parentId": 10,
        "parent": {
            "id": 10,
            "name": "BAA",
            "inactive": false,
            "parentId": 2,
            "parent": {
                "id": 2,
                "name": "BA",
                "inactive": false,
                "parentId": null,
                "parent": null,
                "children": []
            },
            "children": []
        },
        "children": []
    },
,
    {
        "id": 2,
        "name": "BA",
        "inactive": false,
        "parentId": null,
        "parent": null,
        "children": [
            {
                "id": 10,
                "name": "BAA",
                "inactive": false,
                "parentId": 2,
                "children": [
                    {
                        "id": 1011,
                        "name": "BAAA",
                        "inactive": false,
                        "parentId": 10,
                        "children": []
                    }
                ]
            }
        ]
    }

This also has child objects at the root level so I tried this

public async Task<IEnumerable<Office>> GetOffices()
        {
            return await _context.Offices.Where(os => !os.Inactive && !os.ParentId.HasValue).Include(o => o.Parent).Include(o => o.Children).ToListAsync();
        }

And this is the result of the other query

{
        "id": 5,
        "name": "AA",
        "inactive": false,
        "parentId": null,
        "parent": null,
        "children": []
    },
    {
        "id": 2,
        "name": "BA",
        "inactive": false,
        "parentId": null,
        "parent": null,
        "children": [
            {
                "id": 10,
                "name": "BAA",
                "inactive": false,
                "parentId": 2,
                "children": null
            }
        ]
    }

This leaves out id 1010 BAAA child object. Idealy I would like to have it come out as

{
        "id": 5,
        "name": "AA",
        "inactive": false,
        "parentId": null,
        "parent": null,
        "children": []
    },
    {
        "id": 2,
        "name": "BA",
        "inactive": false,
        "parentId": null,
        "parent": null,
        "children": [
            {
                "id": 10,
                "name": "BAA",
                "inactive": false,
                "parentId": 2,
                "children": [
                    {
                        "id": 1011,
                        "name": "BAAA",
                        "inactive": false,
                        "parentId": 10,
                        "children": []
                    }
                ]
            }
        ]
    }

No matter how I stack the query I can't seem to get this to display this way. Is there a way to adjust the query to allow this or an easy post query command to trim only the root level objects that are a child?

score 2 · Accepted Answer · answered Jun 12 '20 at 21:40

2

If you want limited number of child levels (2 in this example) fetched you can try:

await _context.Offices
    .Where(os => !os.Inactive && !os.ParentId.HasValue)
    .Include(o => o.Parent)
    .Include(o => o.Children)
    .ThenInclude(o => o.Children)
    .ToListAsync()

answered Jun 12 '20 at 21:40

Guru Stron

102,774
10
95
132

Thank you. The then-include is what I was missing. While the levels will be dynamic I will cheese with 5 levels because it should never go over that. – Ramious Jun 12 '20 at 22:13
Follow up question to this. My includes don't filter for inactive. Can I use a filter for inactive with this down in the includes? – Ramious Jun 19 '20 at 15:03
@Ramious no, unless this feature is shipped with EF in .NET 5.0. Also if you want just fetch all active hierarchies you can try `(await _context.Offices.Where(os => !os.Inactive).ToListAsync()).Where(os => !os.ParentId.HasValue).ToList()`; – Guru Stron Jun 19 '20 at 15:07
I tried this but I have an inactive true at the third level and still comes up in your new query. – Ramious Jun 19 '20 at 15:16
@Ramious this query will not fetch any inactive data. Take a closer look - here is no includes there. – Guru Stron Jun 19 '20 at 15:17
My bad. My ID10T error. I have this query in two spots depending on the controller. I was testing the wrong one. Query is good. Thank you!! – Ramious Jun 19 '20 at 15:23

Entity Framework Core Parent/Child Clean query with multi level child

1 Answers1