lambda expression trying to query one list based on another

Question

After some advice on how to do this nicely using lambda expressions.

What I want is to get a list of placements based on an agency. I want the placements where the placement.agency != agency but where placement.agencypersonnel contains any of the agency staff.

So where the placement is not for that agency, but there are staff from that agency involved in another agency's placement

I don't know how to query based on the second condition.

So something like:

// agency is being passed in
     var agencySupervisors = agency.AgencyPersonnel;

     return agency.Placements
            .Where(p => p.Supervisors.Contains(agencySupervisors))
            .Where(p => p.Agency != agency);

I get that Contains is supposed to refer to a single object rather than a collection - which is why its erroring.. but I'm not sure how to get it to check against all objects in the collection.

Have also tried Any

 return agency.Placements
           .Where(p => agencySupervisors.Any<PlacementSupervisor>(p.Supervisors))
           .Where(p => p.Agency != agency);

So hopefully its just I'm using the wrong one!!

Another spanner in the works is trying to figure out how the placement supervisor and the agency personnel entities relate to one another.. I think its linked on AgencyPersonnelId = SupervisorId so I'm guessing that will also have to be factored into my expression.

Thanks!

Edit: How do I handle if the type of objects in the two list aren't the same - but I know that the Id will match. Do I have to write a comparer and somehow incorporate that into the expression?? ie. AgencyPersonnelId = SupervisorId

I have tried:

return placements
                .Where(p => p.Supervisors.Any(supervisor => agencySupervisors.Any(ap => ap.AgencyPersonnelId == supervisor.SupervisorId)));

But it is giving me no results so it is obviously wrong.

Edit: Actually when I try to iterate through the placements in the returned collection I'm getting a null reference exception - so I'm not sure if its something to do with my expression or the way I'm returning the results.

Would agency.Placements ever contain placements that doesn't belong to that agengy? If not does Suprvisors have references to all the placements they are involved in? — Mikael Eliasson
– Mikael Eliasson, Commented Dec 23, 2010 at 6:51
Hmm good point thanks! Unfortunately Supervisors don't have references to all the placements they are involved in. — Jen
– Jen, Commented Dec 23, 2010 at 22:09

Kirk Broadhurst · Accepted Answer · 2010-12-23 07:02:42Z

2

You are close with Any & Contains - try both at once

return agency.Placements        
    .Where(p => agencySupervisors.Any(supervisor => p.Supervisors.Contains(supervisor))
    .Where(p => p.Agency != agency);

answered Dec 23, 2010 at 7:02

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gabe · Accepted Answer · 2010-12-23 07:31:08Z

0

I think you can do it with .Intersect also:

return agency.Placements
         .Where(p => agencySupervisors.Intersect(p.Supervisors).Any()
                     && p.Agency != agency);

answered Dec 23, 2010 at 7:31

Gabe

87.1k13 gold badges144 silver badges238 bronze badges

Comments

Jen · Accepted Answer · 2010-12-24 00:38:46Z

Thanks everyone for the help - Because the objects were of different types I ended up having to do something a little different - but then found I was able to use their Ids for the comparison so the result was:

var agencySupervisors = (from ap in agency.AgencyPersonnel
                                    where ap != null
                                        select ap.AgencyPersonnelId).ToList();

            return 
                (from p in m_PlacementRepository.Linq orderby p.PlacementId select p)
                .Where(p => p.Agency != agency)
                .Where(p => p.Supervisors != null && p.Supervisors.Any(s => agencySupervisors.Contains(s.SupervisorId)));

Plus as Mikael rightly pointed out I was starting with the wrong collection in the first place :)

Collectives™ on Stack Overflow

lambda expression trying to query one list based on another

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related