How do I do a table join on two fields in my second table?

Question

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 db.messages on p.person_id equals m.from_id
            join m2 in db.messages 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.

Answer 1

maybe I am missing something but it seems you dont need a join, you say "Give me all messages that have been sent to and from person x (idself)." so if you just want the messages you can work from just he message table, as the id (idself) is known

var MsgPeople from p in AllMessages
                   where p.from_id == idself || p.to_id == idself
                   select p;

ok, try this, you realy need a list of ids for people who sent you messages, and a list of ones that recieved messages from you, then you can select from People any people that exist in that list

var MsgPeople = from p in db.people
                        where (from m in db.messages
                               where m.from_id == selfid
                               select m.to_id).Union(
                        from m in db.messages
                        where m.to_id == selfid
                        select m.from_id).Contains(p.person_id)
                        select p;

another way to do this, one I got from reverse engineering SQL from our DBA when I posed the question to him

 var MsgPeople =  from p in Peoples
     where  p.Person_id != selfid &&
    (from m in Messages
    where m.To_id == selfid select m.From_id).Union(
    from m in Messages
    where m.From_id == selfid select m.To_id).Contains(p.Person_id)
    select p;

Answer 2

maybe try a union

select all the messages sent by user received by me
union 
select all the messages received by user sent by me

Answer 3

People which sent messages to self or recieved messages from self.

from p in People
where p.SentMessages.Any(m => m.to_id == idself)
  || p.ReceivedMessages.Any(m => m.from_id == idself)
select p;

---

If your People don't have a these Messages properties, create the associations.

---

If you want to pull teeth instead... here's the same query without associations.

IQueryable<int> sentQuery = 
  from sent in Messages
  where sent.to_id = idself
  select sent.from_id;

IQueryable<int> receivedQuery =
  from received in Messages
  where received.from_id = idself
  select received.to_id

IQueryble<People> result =
  from p in people
  where System.Linq.Queryable.Concat(receivedQuery, sentQuery)
    .Any(id => id == p.personId)
  select p;