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.