title: Entity Framework + Linq LEFT JOIN using a where clause?
body: <p>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.</p>
  
  <p>Here's the working raw query for the LEFT JOIN (using MySQL database if that matters):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Here's what I have working so far in Linq, but it's an inner join:</p>
  
  <pre><code>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
                       };
  </code></pre>
  
  <p>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?</p>
  
  <p>EDIT:  Please see my own answer below.  </p>