LINQ query to get recursive category structure and products of each category

c# entity-framework-core linq recursion

Question

How can I re-write my query to include all the products of each category in a recursive product category tree? This query gets the categories and their children, so I'm just missing the products:

var categories = _context.ProductCategories
                .Include(e => e.Children)
                .Where(e => e.ParentId == null)
                .ToList();

Entity models:

public class ProductCategory
{
    public int Id { get; set; }
    public int SortOrder { get; set; }
    public string Title { get; set; }
    [ForeignKey(nameof(ParentCategory))]
    public int? ParentId { get; set; }
    // Nav.props:
    public ProductCategory ParentCategory { get; set; }
    public ICollection<ProductCategory> Children { get; set; }
    public List<ProductInCategory> ProductInCategory { get; set; }
}

public class ProductInCategory
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int SortOrder { get; set; }
    public int ProductCategoryId { get; set; }
    // Nav.props:
    public Product Product { get; set; }
    public ProductCategory ProductCategory { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Info { get; set; }
    public decimal Price { get; set; }
    // Nav.props:
    public List<ProductInCategory> InCategories { get; set; }
}

Viewmodel:

public class ViewModelProductCategory
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Title { get; set; }
    public int SortOrder { get; set; }

    public string ProductCountInfo
    {
        get
        {
            return Products != null && Products.Any() ? Products.Count().ToString() : "0";
        }
    }
    public ViewModelProductCategory ParentCategory { get; set; }
    public IEnumerable<ViewModelProductCategory> Children { get; set; }
    public IEnumerable<ViewModelProduct> Products { get; set; }
}

I'm using AutoMapper to convert the query result to viewmodel.

Here is how I'm attempting to render:

Index-view:

@model IEnumerable<MyStore.Models.ViewModels.ViewModelProductCategory>

<ul style="list-style:none;padding-left:0px;">
    @Html.Partial("_CategoryAndProductRecursive", Model)
</ul>

_CategoryAndProductRecursive.cshtml:

@model IEnumerable<MyStore.Models.ViewModels.ViewModelProductCategory>
<ul>
@if (Model != null)
{
    foreach (var item in Model)
    {
    <li>
    @item.Title
        <ul style="list-style:none;">
        @Html.Partial("_CategoryAndProductRecursive.cshtml", item.Children)
        @if (item.Products != null)
        {
            @foreach (var product in item.Products)
            {
                <li>@product.Title</li>
            }
        }
        </ul>
    </li>
    }
}
</ul>
1
1
2/3/2018 11:15:22 AM

Accepted Answer

There are actually two independent questions for the EF Core query part.

First is how to eager load the ProductCategory.ProductInCategory and ProductInCategory.Product navigation properties. The answer to this is simple - use Include / ThenInclude` as explained in the Loading Related Data - Eager Loading section of the documentation.

The second is how to do that recursively for ProductCategory.Children. The trick is to force loading the whole ProductCategory tree in memory, thus letting EF Core navigation property fixup do the parent/child entity linking, and then filter the root entities.

var categories = _context.ProductCategories
    .Include(e => e.ProductInCategory)
        .ThenInclude(e => e.Product)
    .AsEnumerable() // <-- Force full execution (loading) of the above
    .Where(e => e.ParentId == null) // <-- then apply the root filter
    .ToList();

Note that we don't need to include Children with this process. Just be aware of the fact that some Children collections will be null. If that's an issue, just make sure you initialize them in the constructor or with initializer.

public ICollection<ProductCategory> Children { get; set; } = new List<ProductCategory>();
4
2/3/2018 1:45:30 PM

Popular Answer

You could try and use Load instead of Include and loop for a predefined number of times to Load the children each time.

https://msdn.microsoft.com/en-us/library/bb896249.aspx



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow