linq - 将 SQL 子选择转换为 LINQ

Question

我有一个 SQL 子选择查询，它将检索父记录的最新历史记录。

我在将以下内容转换为 LINQ 时遇到问题:

SELECT *  
FROM ProductRequests INNER JOIN ProductRequestHistory  
ON ProductRequests.ID = ProductRequestHistory.ProductRequestsID
AND ProductRequestHistory.ID = (SELECT TOP 1 ProductRequestHistory.ID  
    FROM ProductRequestHistory 
    WHERE ProductRequestHistory.ProductRequestsID = ProductRequestHistory.ID  
    ORDER BY ProductRequestHistory.DateCreated DESC)

这是我尝试过的，但无法编译:

from productrequests in db.ProductRequests
join productrequesthistories in db.ProductRequestHistories
  on new { productrequests.ID, Column1 = (Int32?)Convert.ToInt32(
((from productrequesthistories0 in db.ProductRequestHistories
where
  productrequesthistories0.ProductRequestsID == productrequesthistories0.ID
orderby
  productrequesthistories0.ID descending
select new {
  productrequesthistories0.ID
}).Take(1).First().ID)) }
equals new { ID = (System.Int32?)productrequesthistories.ProductRequestsID, Column1 =    productrequesthistories.ID }
select new {
 productrequests.ID,
 productrequests.ProductRequestNumber,
 productrequests.ProjectID,
 Column1 = productrequesthistories.ID,
 productrequesthistories.Title,
 productrequesthistories.Requester,
 productrequesthistories.TFSNumber,
 productrequesthistories.UseCase,
 productrequesthistories.RequestType,
 productrequesthistories.FunctionalModule,
 productrequesthistories.Description,
 productrequesthistories.Reason,
 productrequesthistories.Priority,
 productrequesthistories.Status,
 productrequesthistories.Release,
 productrequesthistories.Estimate,
 productrequesthistories.Wags,
 productrequesthistories.Confidence,
 productrequesthistories.Notes,
 productrequesthistories.RequirementStatus,
 productrequesthistories.RequirementDoco,
 productrequesthistories.AlphaRequired,
 productrequesthistories.Sprint,
 productrequesthistories.Created,
 productrequesthistories.DateChanged,
 productrequesthistories.Checksum,
 productrequesthistories.ProductRequestsID
}

Answer 1

这在 VB.NET

Dim result = From request in ProductRequests
             Join request2 in ProductRequestHistory
             On request.ID = request2.ProductRequestsID
             And request2.ID = (From request3 in ProductRequestHistory
                                Where request3.ProductRequestsID = request3.ID
                                Order by request3.DateCreated desc
                                Select request3.ID).First()
             Select New With {
                   .request = request
                   .history = request2
             }

在 C#

var result = (from request in db.ProductRequests
              Join request2 in ProductRequestHistory
              On request.ID == request2.ProductRequestsID
              && request2.ID == (from request3 in ProductRequestHistory
                                 where request3.ProductRequestsID == request3.ID
                                 orderby request3.DateCreated desc
                                 select request3.ID).First()
              select new {
                     request = request
                     history = request2
              }
             )

那应该可以解决问题。

另外，在原来的 SQL 语句中，我 raccomend 更改

AND ProductRequestHistory.ID = (SELECT TOP 1 ProductRequestHistory.ID ......

部分到 WHERE条款，因为它不是必需的 JOIN .

因此，在 LINQ 中，您可以使用

Where request2.ID = (From request3 in ProductRequestHistory ......

此外，您可以优化查询预计算 TOP 1 ID值(value):

top1id = (From request3 in ProductRequestHistory
          Where request3.ProductRequestsID = request3.ID
          Order by request3.DateCreated desc
          Select request3.ID).First()

然后使用 Where request2.ID = top1id .