LINQ: Doing Outer Joins (Example Included)

I’m writing this post as a handy resource for those of us that need to reference a quick example of an outer join in LINQ.  I have found it time-consuming to find suitable examples elsewhere.

My example is based on a simple Message entity that has a fishhook association to another Message entity.  The key here is that the relationship defines a 0..1 aggregation and may associate to another Message entity, or not.  When we query a list of Message entities we need to return all Message entities, including both

• those that contain associations to other Message entities, and
• those that do not contain associations to other Message entities

My entities are generated from the Entity Framework, and I use a DTO to return the actual data.  The context is established through a “BusinessEntities”object (as shown in line 3 below).  The outer join is demonstrated in lines 6 – 10 below.  Here is the code example:

public IList<DomainModel.Entities.DTOs.Message> Messages(int recipientPartyId)
{
   var context = new BusinessEntities();
   IQueryable<DomainModel.Entities.DTOs.Message> entities =
   from m in context.Message
   let leftouter = (from msgRef in m.MessageReference
       select new
       {
           MsgRef = msgRef,
       }).FirstOrDefault()
   join recipient in context.Party on m.PartyRecipient.Id equals recipientPartyId
   where
       m.MessageStatusType.Id != MessageSendStatuses.Sent &&
       m.MessageStatusType.Id != MessageSendStatuses.Draft
   select new DomainModel.Entities.DTOs.Message
   {
       Id = m.Id,
       PartySenderId = m.PartySender.Id,
       SenderFirstName = m.PartySender.FirstName,
       SenderLastName = m.PartySender.LastName,
       PartyRecipientId = m.PartyRecipient.Id,
       RecipientFirstName = m.PartyRecipient.FirstName,
       RecipientLastName = m.PartyRecipient.LastName,
       Subject = m.Subject,
       Body = m.Message,
       SentAt = m.CreatedAt,
       MessageTypeId = m.MessageType.Id,
       MessageStatusTypeId = m.MessageStatusType.Id,
       MessageRecipientStatusTypeId = m.MessageRecipientStatusType.Id,
       MessageReference = leftouter.MsgRef != null ? new DomainModel.Entities.DTOs.Message(leftouter.MsgRef) : null
   };

   return entities.OrderByDescending(m => m.SentAt).ToList();
}

In line 30 above, if leftouter.MsgRef != null, we return an initialized Message DTO representing the associated Message entity.  Otherwise, leftouter.MgsRef == null, therefore we return null.

Hope this helps!