How do I do a table join on two fields in my second table?
- by Cannonade
I have two tables:
Messages - Amongst other things, has a to_id and a from_id field.
People - Has a corresponding person_id
I am trying to figure out how to do the following in a single linq query:
Give me all messages that have been sent to and from person x (idself).
I had a couple of cracks at this.
Not quite right
MsgPeople = (from p in db.people
join m in db.messages on p.person_id equals m.from_id
where (m.from_id == idself || m.to_id == idself)
orderby p.name descending
select p).Distinct();
This almost works, except I think it misses one case:
"people who have never received a message, just sent one to me"
How this works in my head
So what I really need is something like:
join m in db.messages on (p.people_id equals m.from_id or p.people_id equals m.to_id)
Gets me a subset of the people I am after
It seems you can't do that. I have tried a few other options, like
doing two joins:
MsgPeople = (from p in db.people
join m in AllMessages on p.person_id equals m.from_id
join m2 in AllMessages on p.person_id equals m2.to_id
where (m2.from_id == idself || m.to_id == idself)
orderby p.name descending
select p).Distinct();
but this gives me a subset of the results I need, I guess something to
do with the order the joins are resolved.
My understanding of LINQ (and perhaps even database theory) is embarrassingly superficial and I look forward to having some light shed on my problem.
