Entity Framework + Linq LEFT JOIN using a where clause?

Question

I am using Linq with Entity Framework 5 and using query syntax (I think, please correct my terminology). I have a table of participants, and want to associate their study ID number with a logged SMS message where either the "to" or "from" number in the message matches the participant's phone number. In addition I want messages sent from (or to) an unknown number to show up in the list as well, in which case the study ID number would be null.

Here's the working raw query for the LEFT JOIN (using MySQL database if that matters):

SELECT
    messages._id, participants.study_id_number, messages.ts,
    messages.from_phone, messages.to_phone, messages.body
FROM messages LEFT JOIN
    participants ON (   (participants.phone_number = messages.to_phone)
                     || (participants.phone_number = messages.from_phone))
ORDER BY messages.ts DESC;

Here's what I have working so far in Linq, but it's an inner join:

var loggedMessages = from pp in theDb.participants
                     let phone = pp.phone_number
                     from mm in theDb.messages
                     let fromPhone = mm.from_phone
                     let toPhone = mm.to_phone
                     where ((phone == fromPhone) || (phone == toPhone))

                     orderby mm.ts descending
                     select new MessageLogEntry()
                     {
                         ParticipantId = pp.study_id_number,
                         TimeStamp = mm.ts,
                         FromPhone = fromPhone,
                         ToPhone = toPhone,
                         Body = mm.body
                     };

I'm new to this, so I'd be happy with a link to a tutorial on Linq queries, but what would I need to add to make it a LEFT JOIN?

EDIT: Please see my own answer below.

Solution 1

After some quick and shallow study, plus a few more SO links, I have the following generating the desired result. Comments are very welcome, the biggest thing I learned from this exercise is how much I have to learn!

var loggedMessages = from mm in theDb.messages
                     from pp in theDb.participants
                     .Where(pp1 => ((mm.from_phone == pp1.phone_number) || (mm.to_phone == pp1.phone_number)))
                     .DefaultIfEmpty()
                     orderby mm.ts descending
                     select new MessageLogEntry()
                     {
                         ParticipantId = (int?)pp.study_id_number,
                         TimeStamp = mm.ts,
                         FromPhone = mm.from_phone,
                         ToPhone = mm.to_phone,
                         Body = mm.body
                     };

I'm slightly horrified by the way it goes from query syntax to method syntax and back, but it compiled, LinqPad liked it and showed the right result, and it tests out. I have no idea how performant it is compared to how performant it could be, that's for a future lesson.

And LinqPad is awesome!

Solution 2

You need to add DefaultIfEmpty() to make it a left join.

Check this:

var loggedMessages = from pp in theDb.participants
                     join mm in theDb.messages
                     on pp.phone_number equals mm.to_phone || 
                     pp.phone_number equals mm.from_phone
                     into joinedmm
                     from pm in joinedmm.DefaultIfEmpty()
                     orderby mm.ts descending
                     select new MessageLogEntry()
                     {
                         ParticipantId = pp.study_id_number,
                         TimeStamp = pm.ts,
                         FromPhone = fromPhone,
                         ToPhone = toPhone,
                         Body = pm.body
                     };