Filtering collection of children in Entity Framework with Lambda

Posted on

Question :

I have a case where I need to bring all the items of the parent object where it has a certain condition, for example:

    .Where(x=> x.Ativo == true)

I’ve tried using Any, but looking at SQL it creates an Exists and that’s not what I wanted, I wanted to bring the active parent and all of their children active too.

How to do it?


Answer :

If I understand correctly, you can try this:

.Where(x => x.Ativo && !x.Filhos.Any(e => !e.Ativo));

From what I understand, you want all the active children:

pais.Where(e => e.Ativo)
            .Select(s => new Pai() { Filhos = s.Filhos.Where(e => e.Ativo).ToList() });


from pai in ctx.Pai
join filho in ctx.Filho on pai.Id equals filho.IdPai
where pai.Ativo && filho.Ativo
select pai

If returning the parent is not enough, you can use new in select and mount the object as you see fit or just return the child.

EDIT: I kind of figured out what the entities are like, maybe I have to make a small adjustment to the association.

EDIT2: Because the scenario is ManyToMany the use of two from can solve your problem.

from pai in ctx.Pai
from filho in pai.Filhos
where filho.ativo && pai.ativo
select pai

Remembering that select must be the type you want the query to return, or a new type you can create.


Leave a Reply

Your email address will not be published. Required fields are marked *