I have a startup project in ASP.NET Core Web API project using Entity Framework Core.
I have a Category object that has a parent child relationship with Category (ie: categories and their subcategories and subcategories can have subcategories, etc).
When I try to return only the top level categories (hoping their child categories will get added to the graph automatically) I find that it does return the top level categories and their children but not their children's children, ie:
top level -> child -> [should return child's child but its not]
I am attempting to use this method on my CategoryRepository class:
public async Task<IEnumerable<Category>> getAllTopLevelCategories()
{
return await _context.Categories
.Include(p => p.Children)
.Where(category => category.ParentId == null)
.ToListAsync();
}
But my JSON returned is missing "Steer Compost" which is a child of the Category "Compost"
[
{
"name": "Garden",
"parentId": null,
"parent": null,
"children": [
{
"name": "Compost",
"parentId": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"parent": null,
"children": [],
"products": [],
"id": "464b75f0-f5db-4fac-8023-ec39ae21ad79",
"dateAdded": "2024-10-14T20:51:57.167129Z"
}
],
"products": [],
"id": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"dateAdded": "2024-08-07T13:59:27.182185Z"
},
{
"name": "Pets",
"parentId": null,
"parent": null,
"children": [
{
"name": "Pet Food",
"parentId": "f1dfe34c-adb2-4e19-81ad-d69d4c3364b9",
"parent": null,
"children": [],
"products": [],
"id": "b1000daf-d101-4ee3-b5b6-fb49e4aa5598",
"dateAdded": "2024-10-14T17:36:29.502663Z"
}
],
"products": [],
"id": "f1dfe34c-adb2-4e19-81ad-d69d4c3364b9",
"dateAdded": "2024-08-07T14:17:44.973453Z"
}
]
This method returns all Categories but the object graph for each is completely filled out! I basically want this but only to return the top level Categories (and the object graphs they are a part of). I COULD use this one but I would have to filter (using Linq) on the Category array returned so I would only be working with the top level categories but it seems like this is something that Entity Core should be able to do and I just don't know how to do it yet.
public async Task<IEnumerable<Category>> getAllCategories()
{
return await _context.Set<Category>()
.ToListAsync();
}
Result:
[
{
"name": "Garden",
"parentId": null,
"parent": null,
"children": [
{
"name": "Compost",
"parentId": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"parent": null,
"children": [
{
"name": "Steer Compost",
"parentId": "464b75f0-f5db-4fac-8023-ec39ae21ad79",
"parent": null,
"children": [],
"products": [],
"id": "5d567504-62be-4af7-9cab-dcdd66b670ec",
"dateAdded": "2024-10-18T15:09:45.58668Z"
}
],
"products": [],
"id": "464b75f0-f5db-4fac-8023-ec39ae21ad79",
"dateAdded": "2024-10-14T20:51:57.167129Z"
}
],
"products": [],
"id": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"dateAdded": "2024-08-07T13:59:27.182185Z"
},
{
"name": "Pets",
"parentId": null,
"parent": null,
"children": [
{
"name": "Pet Food",
"parentId": "f1dfe34c-adb2-4e19-81ad-d69d4c3364b9",
"parent": null,
"children": [],
"products": [],
"id": "b1000daf-d101-4ee3-b5b6-fb49e4aa5598",
"dateAdded": "2024-10-14T17:36:29.502663Z"
}
],
"products": [],
"id": "f1dfe34c-adb2-4e19-81ad-d69d4c3364b9",
"dateAdded": "2024-08-07T14:17:44.973453Z"
},
{
"name": "Pet Food",
"parentId": "f1dfe34c-adb2-4e19-81ad-d69d4c3364b9",
"parent": {
"name": "Pets",
"parentId": null,
"parent": null,
"children": [
null
],
"products": [],
"id": "f1dfe34c-adb2-4e19-81ad-d69d4c3364b9",
"dateAdded": "2024-08-07T14:17:44.973453Z"
},
"children": [],
"products": [],
"id": "b1000daf-d101-4ee3-b5b6-fb49e4aa5598",
"dateAdded": "2024-10-14T17:36:29.502663Z"
},
{
"name": "Compost",
"parentId": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"parent": {
"name": "Garden",
"parentId": null,
"parent": null,
"children": [
null
],
"products": [],
"id": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"dateAdded": "2024-08-07T13:59:27.182185Z"
},
"children": [
{
"name": "Steer Compost",
"parentId": "464b75f0-f5db-4fac-8023-ec39ae21ad79",
"parent": null,
"children": [],
"products": [],
"id": "5d567504-62be-4af7-9cab-dcdd66b670ec",
"dateAdded": "2024-10-18T15:09:45.58668Z"
}
],
"products": [],
"id": "464b75f0-f5db-4fac-8023-ec39ae21ad79",
"dateAdded": "2024-10-14T20:51:57.167129Z"
},
{
"name": "Steer Compost",
"parentId": "464b75f0-f5db-4fac-8023-ec39ae21ad79",
"parent": {
"name": "Compost",
"parentId": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"parent": {
"name": "Garden",
"parentId": null,
"parent": null,
"children": [
null
],
"products": [],
"id": "e9a9e63b-e71f-4aab-b1bb-97db5687048b",
"dateAdded": "2024-08-07T13:59:27.182185Z"
},
"children": [
null
],
"products": [],
"id": "464b75f0-f5db-4fac-8023-ec39ae21ad79",
"dateAdded": "2024-10-14T20:51:57.167129Z"
},
"children": [],
"products": [],
"id": "5d567504-62be-4af7-9cab-dcdd66b670ec",
"dateAdded": "2024-10-18T15:09:45.58668Z"
}
]
And here is my Category object:
using Newtonsoft.Json;
using ShopBack.Database.Models;
/// <summary>
/// The Category model
/// </summary>
public class Category : ModelBase
{
/// <summary>
/// Name of the category
/// </summary>
public required string Name { get; set; }
/// <summary>
/// Parent CategoryId
/// </summary>
public required Guid? ParentId { get; set; }
/// <summary>
/// Parent CategoryId
/// </summary>
public virtual Category? Parent { get; set; }
/// <summary>
/// Parent CategoryId
/// </summary>
public virtual ICollection<Category> Children { get; set; } = new List<Category>();
/// <summary>
/// List of products for the category
/// </summary>
public virtual ICollection<Product> Products { get; set; } = new List<Product>();
}
You didn't tell it to load 2 child levels, only 1. If you want top -> child -> grandchild then:
return await _context.Categories
.Include(p => p.Children)
.ThenInclude(c => c.Children)
.Where(category => category.ParentId == null)
.ToListAsync();
Just keep in mind it will stop there. It won't include great-grandchildren.
Where you might see it populate some grandchildren even when you didn't specify including them has to do with EF's caching. If a grandchild or any other related entity happens to be loaded and tracked by the DbContext when you read this query then that entity will be automatically associated when you perform a tracking-enabled query. This can be misleading in results because if you had 2 grandchildren to "Compost" but one happened to have been read and tracked earlier but the other hadn't, then Compost would have filled with just one grandchild.
To avoid tracking cache populating entities that haven't explicitly been requested, perform a No-Tracking query:
return await _context.Categories
.Include(p => p.Children)
.THenInclude(c => c.Children)
.Where(category => category.ParentId == null)
.AsNoTracking()
.ToListAsync();
This has two effects. #1 - Entities fetched by this query are not added to the tracking cache. #2 - In building the results for this query, entities in the tracking cache are not used. (only the current persisted data in the DB) I recommend that when reading data you should use AsNoTracking and leave tracking queries solely for update/insert reads.
Depending on the number of rows and size (columns) being returned you might also benefit from using .AsSplitQuery() to eliminate the Cartesian Product produced when joining several tables.