c# - 如何使用 Linq to SQL 根据另一个表的前 1 个 ID 进行连接？

Question

现在，我收到以下错误:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

这是我的查询:

var issues = from issue in this.IssueDatas
             join original in this.NoteDatas on issue.NoteDatas
                                                     .OrderBy(n => n.CreatedDate)
                                                     .Select(n => n.NoteId)
                                                     .First() equals original.NoteId
             join current in this.NoteDatas on issue.NoteDatas
                                                    .OrderByDescending(n => n.CreatedDate)
                                                    .Select(n => n.NoteId)
                                                    .First() equals current.NoteId
             select { whatever, i, want, to, select }

获取那些 TOP 1 id 的 SQL 部分是这样的:

SELECT whatever, i, want, to
FROM [dbo].[issue_Details] AS [t0]
INNER JOIN [dbo].[issue_notes] AS [t1] ON ((
    SELECT TOP (1) [t3].[id]
    FROM (
        SELECT [t2].[id], [t2].[issue_id]
        FROM [dbo].[issue_notes] AS [t2]
        ORDER BY [t2].[CreatedDate]
        ) AS [t3]
    WHERE [t3].[issue_id] = [t0].[IssueDetailsId]
    )) = [t1].[id]
INNER JOIN [dbo].[issue_notes] AS [t4] ON ((
    SELECT TOP (1) [t6].[id]
    FROM (
        SELECT [t5].[id], [t5].[issue_id]
        FROM [dbo].[issue_notes] AS [t5]
        ORDER BY [t5].[CreatedDate] DESC
        ) AS [t6]
    WHERE [t6].[issue_id] = [t0].[IssueDetailsId]
    )) = [t4].[id]

...但它应该看起来更像这样:

FROM [dbo].[issue_Details] AS [t0]
INNER JOIN [dbo].[issue_notes] AS [t1] ON (
    SELECT TOP (1) [t2].[id]
    FROM [dbo].[issue_notes] AS [t2]
    ORDER BY [t2].[CreatedDate]
    WHERE [t2].[issue_id] = [t0].[IssueDetailsId]
    ) = [t1].[id]
INNER JOIN [dbo].[issue_notes] AS [t4] ON (
    SELECT TOP (1) [t5].[id]
    FROM [dbo].[issue_notes] AS [t5]
    ORDER BY [t5].[CreatedDate] DESC
    WHERE [t5].[issue_id] = [t0].[IssueDetailsId]
    ) = [t4].[id]

我试过使用 this.NoteDatas 而不是 issue.NoteDatas 并手动应用 id 过滤器，我试过选择第一个音符然后取 id (反转我上面输入的内容)，我尝试使用 Take(int) 而不是 First()...我只是不知道该怎么做. LINQ 读取起来比它生成的 SQL 更直接。

Answer 1

这就是我最终更改 LINQ 使其工作的方式:

var issues = from issue in this.IssueDatas
             join original in this.NoteDatas on issue.NoteDatas
                                                     .Min(n => n.CreatedDate) equals original.CreatedDate
             join current in this.NoteDatas on issue.NoteDatas
                                                    .Max(n => n.CreatedDate) equals current.CreatedDate
             select { whatever, i, want, to, select }