C# Entity Framework Core - Get Parent with filtered child collections

Question

I have approximately next domain models:

public partial class ParentEntity
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;

    public virtual ICollection<ChildA> ChildAs { get; set; } = new List<ChildA>();
    public virtual ICollection<ChildAHistory> ChildAHistories { get; set; } = new List<ChildAHistory>();
}

public partial class ChildA
{
    public int Id { get; set; }
    public int ParentEntityId { get; set; }
    public string Data1 { get; set; } = null!;
    public string Data2 { get; set; } = null!;
    public string Comments { get; set; }

    public virtual ParentEntity ParentEntity { get; set; } = null!;
    public virtual ICollection<ChildAHistory> ChildAHistories { get; set; } = new List<ChildAHistory>();
}

public partial class ChildAHistory
{
    public int Id { get; set; }
    public int ParentEntityId { get; set; }
    public int ChildAId { get; set; }
    public string Data1 { get; set; } = null!;
    public string Data2 { get; set; } = null!;
    public string Comments { get; set; }

    public virtual ParentEntity ParentEntity { get; set; } = null!;
    public virtual ChildA ChildA { get; set; } = null!;
}

Let's say I have the following data in DB:

// parent #1
{
  "id": 11,
  "name": "parent1",
  "childAs": [...],
  "childAHistories": [...]
}
// parent #2
{
  "id": 22,
  "name": "parent2",
  "childAs": [...],
  "childAHistories": [...]
}
// parent #1 \ child #1
{
  "id": 1,
  "parentEntityId": 11,
  "data1": "aa",
  "data2": "text2",
  "comments": "",
  "parentEntity": "<parent object>",
  "childAHistories": [
    {
      "id": 2,
      "parentEntityId": 11,
      "childAId": 1,
      "data1": "tt",
      "data2": "aa",
      "comments": "",
      "parentEntity": "<parent object>",
      "childA": "<childA object>"
    },
    {
      "id": 3,
      "parentEntityId": 11,
      "childAId": 1,
      "data1": "aa",
      "data2": "bb",
      "comments": "",
      "parentEntity": "<parent object>",
      "childA": "<childA object>"
    },
    {
      "id": 4,
      "parentEntityId": 11,
      "childAId": 1,
      "data1": "aa",
      "data2": "aa",
      "comments": "",
      "parentEntity": "<parent object>",
      "childA": "<childA object>"
    }
  ]
}
// parent #2 \ child #2
{
  "id": 2,
  "parentEntityId": 22,
  "data1": "bb",
  "data2": "text2",
  "comments": "",
  "parentEntity": "<parent object>",
  "childAHistories": [
    {
      "id": 5,
      "parentEntityId": 22,
      "childAId": 2,
      "data1": "tt",
      "data2": "aa",
      "comments": "",
      "parentEntity": "<parent object>",
      "childA": "<childA object>"
    },
    {
      "id": 6,
      "parentEntityId": 22,
      "childAId": 2,
      "data1": "bb",
      "data2": "bb",
      "comments": "",
      "parentEntity": "<parent object>",
      "childA": "<childA object>"
    }
  ]
}

I need to return while collection of parents and child (ChildA) that includes sub-child (ChildAHistory) and at the same time filter sub-childs based on criteria. Such as return collection on parents with childs and historical records that contains "data1" = "aa" and filters out all other sub-childs from there.

I tried of using .Any(o => o..), but it returns only boolean, so as result it returns everything and doesn't filter the data.

var response = _context.ParentEntities
                   .Include(o => o.ChildAs)
                   .ThenInclude(o => o.ChildAHistories)
                   .Where(o => o.ChildAs.Any(o => o.Data1 == "aa"))
                   .Where(o => o.ChildAHistories .Any(o => o.Data1 == "aa"));

Not sure if I'm looking on my problem from right perspective. Any advice are welcome!

Answer 1

For filtering out like that you should't use where method, you should use Select in these situations, like below:

var response = _context.ParentEntities
    .Select(parent => new 
    {
        Id = parent.Id,
        Name = parent.Name,
        ChildAs = parent.ChildAs
            .Where(childA => childA.Data1 == "aa")
            .Select(childA => new 
            {
                Id = childA.Id,
                ParentEntityId = childA.ParentEntityId,
                Data1 = childA.Data1,
                Data2 = childA.Data2,
                Comments = childA.Comments,
                ChildAHistories = childA.ChildAHistories
                    .Where(history => history.Data1 == "aa")
                    .ToList()
            })
            .ToList()
    })
    .Where(parent => parent.ChildAs.Any())
    .ToList();

Inside the select method itself, you should add the needed conditions.