In Linq, How to get list of parents that their children have a special age and other childern remove too?

Question

I have two models: Parent and Child

public class Parent
{
    public int ID { get; set; }
    public string Name{ get; set; }


    public virtual ICollection Children { get; set; }
}
public class Child
{
    public int ID { get; set; }
    public int ParentID { get; set; }
    public string Name{ get; set; }
    public int Age{get; set;}

    [ForeignKey("ParentID")]
    public virtual Parent Parent { get; set; }
}

Sample data:

Parent            Child  

ID      Name     ID    Name    Age  ParentID
1       a        1       a1     10     1
2       b        2       a2     20     1
3       c        3       a3     25     1
4       d        4       b1     30     2
5       e        5       b2     35     2
                 6       c1     27     3
                 7       d1     10     4
                 8       d2     18     4
                 9       d3     26     4
                 10      e1     15     5

I want to show a list of parents that have any child older than a specific age, and this list would be joined children model, and in children subset, just be showed the one with specific age.

Desired output:

parents
ID      Name  Children.ID Children.Name   Age  
1       a        3            a3          25     
2       b        4            b1          30     
3       c        5            b2          35     
4       d        6            c1          27     
                 9            d3          26     
                 

Note: The children table is in parent's fields.

I tested this code but this showed parents with all children:

var parents = context.Parents.Include(p => p.Children).ToList();

  // some processes on parents...

parents = parents.Where(p => p.Children.Any(c => c.Age >= 25)).ToList();

Output:

parents
ID      Name  Children.ID Children.Name  Age  
1       a        1                a1     10     
2       b        2                a2     20     
3       c        3                a3     25     
4       d        4                b1     30     
                 5                b2     35     
                 6                c1     27     
                 7                d1     10     
                 8                d2     18     
                 9                d3     26

 
                     

How do I Filter the Children in Linq query?

Answer 1

You can filter the related entities as shown in Filtered Include.

var parents = context.Parents
    .Include(p => p.Children
        .Where(c => c.Age >= 25))
    .Where(p => p.Children.Any(c => c.Age >= 25))
    .ToList();

---

Updated:

As a requirement, you need to retrieve all the Parent data and filter after some processing steps. You should iterate each parent and overwrite the Children value with the filtered Children data.

var parents = context.Parents.Include(p => p.Children).ToList();

...

parents = parents.Where(p => p.Children.Any(c => c.Age >= 25)).ToList();

parent.ForEach(p => 
{ 
    p.Children = p.Children
        .Where(c => c.Age >= 25)
        .ToList(); 
});