asp.net core EF select all descendants

Question

I have 2 models:

public class Document
{
    public Guid Id { get; set; }

    [Required(ErrorMessage = "Please enter new documents name . . .")]
    [Display(Name = "Document Name:")]
    public string Name { get; set; }

    [Required]
    [Display(Name = "Type:")]
    [EnumDataType(typeof(DocumentTypeEnum))]
    public DocumentTypeEnum DocType { get; set; }

    public DateTime? CreatedDate { get; set; }

    public string? Description { get; set; }

    public virtual ICollection<DocumentVersion>? Versions { get; set; }

    [Required]
    [Display(Name = "Document Category")]
    public virtual DocumentCategory Category { get; set; }
}

And

public class DocumentCategory
{
    public int Id { get; set; }

    public string? Name { get; set; }

    public int? ParentId { get; set; }

    public virtual DocumentCategory? Parent { get; set; }

    public virtual ICollection<DocumentCategory>? Children { get; set; }
}

The document categories are hierarchal and represented in the database like below:

ID      |  Name       | Parent ID
1       |  Test1      | 'null'
2       |  Test2      | 'null'
3       |  Test3      | 'null'
4       |  Test1.1    |   1
5       |  Test1.2    |   1
6       |  Test1.1.1  |   4
7       |  Test1.1.2  |   4

In the controller I have an action which returns a partial view, which selects documents based on the category ID using the below code

public async Task<ActionResult> DocsByCatAsync(int id)
{
    var documents = new List<Document>();
    if (id == 0)
    {
        documents = await _documentDbContext
            .Documents
            .AsNoTracking()
            .Include(d => d.Category)
            .ToListAsync();
    }
    else
    {
        documents = await _documentDbContext
            .Documents
            .Where(d => d.Category.Id == id)
            .AsNoTracking()
            .Include(d => d.Category)
            .ToListAsync();
    }

    return PartialView("_DocumentTable", documents);
}

What I am trying to do is if I select a CategoryId which has descendants, I can return all documents in the selected ID and the category descendants.

For example, if the category ID was 1, then it would return all documents with a category ID of : 1, 4, 6, 7 (using the example table above).

Something like

documents = await _documentDbContext
    .Documents
    .Where(d => d.Category.Id IN (1,4,6,7));

The issue is, there isn't a fixed number of levels, nor can I work out how to use EF to include an IN statement.

Any advice?

Your ActionResult is an array. See following posting with answer that has 157 : https://stackoverflow.com/questions/9508265/how-do-i-accept-an-array-as-an-asp-net-mvc-controller-action-parameter?force_isolation=true — jdweng, Sep 29 '22 at 10:39
The `IN` part can be achived with `.Where(d => new int[] { 1, 4, 6, 7 }.Contains(d.Category.Id))` — Ivan Gechev, Sep 29 '22 at 10:41
Which RDBMS are you using? In Sql Server there is [hierarchyid](https://learn.microsoft.com/en-us/sql/relational-databases/tables/tutorial-using-the-hierarchyid-data-type?view=sql-server-ver16) data type. See [sample](https://www.meziantou.net/using-hierarchyid-with-entity-framework-core.htm) — Alexander Petrov, Sep 29 '22 at 13:17
You are using a so-called Adjacency List. To work effectively with it, common table expressions are needed. EF does not have their support. The [linq2db](https://linq2db.github.io/articles/sql/CTE.html) framework has such support. At least use the [linq2db.EntityFrameworkCore](https://github.com/linq2db/linq2db.EntityFrameworkCore) extension. It is listed in the official list [EF Core Tools & Extensions](https://learn.microsoft.com/en-us/ef/core/extensions/) — Alexander Petrov, Sep 29 '22 at 13:21

score 0 · Accepted Answer · answered Sep 29 '22 at 15:39

Thanks for the advice.

The mistake I was making was with the .where() clause. I was trying to write it .where(d => d.category.Id.Contains() which wasn't working.

Ivan's comment is what I needed.

Essentially I have a recursive process which generates an array of ID's which I pass into the EF 'query':

var catIDs = GetChildrenCategories(id);
        
documents = await _documentDbContext.Documents.Where(d => catIDs.Contains(d.Category.Id))
                .AsNoTracking()
                .Include(d => d.Category)
                .ToListAsync();

asp.net core EF select all descendants

1 Answers1