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?

Answer 1

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();