I have a case where I need to bring all the items of the parent object where it has a certain condition, for example:
ctx.Pai
.Where(x=> x.Ativo == true)
.Include(Filhos)
.Where(TrazerApenasFilhosAtivos);
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?
If I understand correctly, you can try this:
Pai
.Include(Filhos)
.Where(x => x.Ativo && !x.Filhos.Any(e => !e.Ativo));
EDIT: 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.