LINQ nested joins

Question

Im trying to convert a SQL join to LINQ. I need some help in getting the nested join working in LINQ.

This is my SQL query, Ive cut it short just to show the nested join in SQL:

select distinct 
 txtTaskStatus as TaskStatusDescription, 
    txtempfirstname+ ' ' + txtemplastname as RaisedByEmployeeName,
    txtTaskPriorityDescription as TaskPriorityDescription,
    dtmtaskcreated as itemDateTime, 
    dbo.tblTask.lngtaskid as TaskID, 
    dbo.tblTask.dtmtaskcreated as CreatedDateTime, 
    convert(varchar(512), dbo.tblTask.txttaskdescription) as ProblemStatement,
    dbo.tblTask.lngtaskmessageid, 
    dbo.tblMessage.lngmessageid as MessageID, 
    case when isnull(dbo.tblMessage.txtmessagesubject,'') <> '' then txtmessagesubject else left(txtmessagedescription,50) end as MessageSubject, 
    dbo.tblMessage.txtmessagedescription as MessageDescription, 
    case when dbo.tblMessage.dtmmessagecreated is not null then dbo.tblMessage.dtmmessagecreated else CAST(FLOOR(CAST(dtmtaskcreated AS DECIMAL(12, 5))) AS DATETIME) end as MessageCreatedDateTime

FROM 
 dbo.tblAction RIGHT OUTER JOIN dbo.tblTask ON dbo.tblAction.lngactiontaskid = dbo.tblTask.lngtaskid 
 LEFT OUTER JOIN dbo.tblMessage ON dbo.tblTask.lngtaskmessageid = dbo.tblMessage.lngmessageid 
 LEFT OUTER JOIN dbo.tblTaskCommentRecipient 
  RIGHT OUTER JOIN  dbo.tblTaskComment ON dbo.tblTaskCommentRecipient.lngTaskCommentID = dbo.tblTaskComment.lngTaskCommentID 
  ON dbo.tblTask.lngtaskid = dbo.tblTaskComment.lngTaskCommentTaskId

Answer 1

A more seasoned SQL programmer wouldn't join that way. They'd use strictly left joins for clarity (as there is a strictly left joining solution available).

I've unraveled these joins to produce a hierarchy:

Task
  Action
  Message
  TaskComment
    TaskCommentRecipient

With associations created in the linq to sql designer, you can reach these levels of the hierarchy:

  //note: these aren't outer joins
from t in db.Tasks
let actions = t.Actions
let message = t.Messages
let comments = t.TaskComments
from c in comments
let recipients = c.TaskCommentRecipients

DefaultIfEmpty produces a default element when the collection is empty. Since these are database rows, a default element is a null row. That is the behavior of left join.

query =
(
  from t in db.Tasks
  from a in t.Actions.DefaultIfEmpty()
  from m in t.Messages.DefaultIfEmpty()
  from c in t.Comments.DefaultIfEmpty()
  from r in c.Recipients.DefaultIfEmpty()
  select new Result()
  {
    TaskStatus = ???
    ...
  }
).Distinct();

---

Aside: calling Distinct after a bunch of joins is a crutch. #1 See if you can do without it. #2 If not, see if you can eliminate any bad data that causes you to have to call it. #3 If not, call Distinct in a smaller scope than the whole query.

Hope this helps.