LINQ嵌套联接 [英] LINQ nested joins

问题描述

我正在尝试将SQL连接转换为LINQ.我需要一些帮助来使嵌套联接在LINQ中工作.

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

这是我的SQL查询，我已将其简化为仅显示SQL中的嵌套联接:

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

推荐答案

经验丰富的SQL程序员不会采用这种方式.为了清楚起见，他们会使用严格的左联接(因为有可用的严格的左联接解决方案).

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

使用在linq to sql designer中创建的关联，您可以达到以下层次结构级别:

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会生成一个默认元素.由于这些是数据库行，因此默认元素为空行.这就是左联接的行为.

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();

---

另外:在一堆联接之后调用Distinct是一个拐杖. #1看看是否可以没有它. #2如果不是，请查看是否可以消除任何导致您不得不调用的不良数据. #3如果不是，请在比整个查询小的范围内调用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.

希望这会有所帮助.

这篇关于LINQ嵌套联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！