Filtering Kids Collection in Entity Framework with Lambda

Asked

Viewed 684 times

3

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

ctx.Pai
    .Where(x=> x.Ativo == true)
    .Include(Filhos)
    .Where(TrazerApenasFilhosAtivos);

I tried to use with Any, but looking at SQL it creates an Exists and that’s not what I wanted, I wanted to bring active father and all his children also active.

How to do?

c# entity-framework

  • You want him to just return the children and not the father, right?

  • Tries .Include(Filhos.Where(condicao));

  • If what you want is a kind of Left Join, with Lambda using Include is not possible. Maybe your solution is with Linq (from p in context.Pai ...) or GroupJoin with Lambda.

  • Groupjoin (Left Join) example: https://www.youtube.com/watch?v=4JspxnEAE-M

  • Another example of Left Join, but Linq: https://www.youtube.com/watch?v=ksGHiGg405M

2 answers

4


If I understand correctly, you can try it this way:

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

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

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

  • But so he returns to me all children regardless of whether they are active or not, if there is any incidence of an active child.

  • @Kevin, in the example I did, he brings only if he has all the active children (!x.Filhos.Any(e => !e.Ativo)). I reversed the logic of .Any() that you tried to.

  • Ah, but what I’m trying to bring only active children, if there are inactive ones who don’t come, but others must come, you know ?

  • It’s hard to understand. Do you want only children to come who are active in that father? Or just the father who has ALL active children?

  • @Kevin, I’ve made an issue, take a look if it fits.

  • 1

    Thank you Filipe, this way it works!

  • Not at all! If the answer has answered your question, it’s nice to mark as the answer to help anyone who needs a similar solution.

  • Let’s go continue this discussion in chat.

Show 3 more comments

1

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 father is not enough, you can use one new in the select and assemble the object the way you think best or just return the child.

EDIT: I imagined more or less how the entities are, maybe I have to make a small adjustment in the association.

EDIT2: how 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

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

  • And in case Son is a Christian ?

  • I imagine that in the Son you have a navigation property for his father. If you have it you can use it, if you don’t have it I think you should have it. How this association is made?

  • It is Many to Many: Pai { public string Id {get;set;} public Icollection<Filho> Filhos {get;set;} public bool Active {get;set;} Filho { public string Id {get;set;} public Icollection<Pai> Pais {get;set;} public bool Active {get;set;} }

  • Then you can make two from, I’ll edit the original answer.

  • It worked just like Query, but even so the filtered items are not returned in the parent object, do you have any way to do this or do I need to give a new ? Here’s how my query looks: var monitoring = ( from mon in _context.Monitoring from group in mon.Groups Where group.Status == Statusgroup.Active && mon.Id == id && mon.Status == Statusmonitoring.Monitoring select new { mon, group}). Firstordefault(); .

  • Maybe you can set the group in mon within new. mon.Groups = Clothes.

  • Let’s go continue this discussion in chat.

Show 2 more comments

Browser other questions tagged c# entity-framework

You are not signed in. Login or sign up in order to post.