CODEWITHSUNDEEP
c#entity-framework-core
Akmal Zamel
Akmal Zamel

Reputation: 43

Entity Framework Core: Get parent with all child entities where exists a child with specific criteria

I have a parent entity which contains a list of children.

public class Parent
{
    public int Id { get; set; }
    public List<Child> Children { get; set; }
}

public class Child
{
    public int Id { get; set; }
    public string Type { get; set; }
}

EFcore will create a ParentId as a foreign key in the table Child.

Let's assume that I have several child entities with different Type string. I want to retrieve the parent entity including all child entities regardless of the Type string (i.e "Alpha", "Beta", "Charlie", etc.). But first, I want to filter the child entity according to specific Type string, similar to exists in SQL query.

select * from Parent inner join Child ON Parent.Id = Child.ParentId
where exists (select * from Parent where Parent.Id = Child.ParentId AND Type = 'Alpha')

How do I achieve this using EFCore?

Upvotes: 1

Views: 4335

Answers (1)

Roman
Roman

Reputation: 12171

You can use Any():

_dbContext.Parents
    .Include(p => p.Children)
    .Where(p => p.Children.Any(c => String.Equals(c.Type, "Alpha")))
    .ToList(); // or use await and ToListAsync()

So it queries parents (including children) that has at least one child with type equal to Alpha.

Upvotes: 2

Related Questions