Linq to SQL |按日期排序的前5个不同顺序

Question

我有一个SQL查询，我想在asp.net应用程序中从LINQ调用到SQL。

SELECT TOP 5 *
FROM   (SELECT SongId,
               DateInserted,
               ROW_NUMBER()
                 OVER(
                   PARTITION BY SongId
                   ORDER BY DateInserted DESC) rn
        FROM   DownloadHistory) t
WHERE  t.rn = 1
ORDER  BY DateInserted DESC 

我不知道通过linq到sql是否可能，如果不可能，请提供其他方法。

Answer 1

我认为您必须通过将SQL分区更改为Linq组。（实际上，分区所做的就是按歌曲分组，并为每个组选择最新的一行。）

IEnumerable<DownloadHistory> top5Results = DownloadHistory
    // group by SongId
    .GroupBy(row => row.SongId)
    // for each group, select the newest row
    .Select(grp => 
        grp.OrderByDescending(historyItem => historyItem.DateInserted)
        .FirstOrDefault()
    )
    // get the newest 5 from the results of the newest-1-per-song partition
    .OrderByDescending(historyItem => historyItem.DateInserted)
    .Take(5);

Answer 2

虽然McGarnagle的答案解决了这个问题，但当我看到这两个查询的执行计划时，我真的很惊讶地发现linq-to-sql与原生sql查询相比真的太慢了。请参阅为上述linq-to-sql:生成的查询

--It took 99% of the two execution
SELECT TOP (5) [t3].[SongId], [t3].[DateInserted]
    FROM (
        SELECT [t0].[SongId]
        FROM [dbo].[DownloadHistory] AS [t0]
        GROUP BY [t0].[SongId]
        ) AS [t1]
    OUTER APPLY (
        SELECT TOP (1) [t2].[SongId], [t2].[DateInserted]
        FROM [dbo].[DownloadHistory] AS [t2]
        WHERE [t1].[SongId] = [t2].[SongId]
        ORDER BY [t2].[DateInserted] DESC
        ) AS [t3]
    ORDER BY [t3].[DateInserted] DESC

--It took 1% of the two execution
SELECT TOP 5 t.SongId,t.DateInserted
    FROM   (SELECT SongId,
               DateInserted,
               ROW_NUMBER()
                 OVER(
                   PARTITION BY SongId
                   ORDER BY DateInserted DESC) rn
        FROM   DownloadHistory) t
    WHERE  t.rn = 1
    ORDER  BY DateInserted DESC