带有Linq-to-entities的SQL行号

Question

我正在将我的项目转换为使用EF，并希望将存储过程转换为Linq- to -entities查询。

下面是我的SQL查询(简单版本)，我在转换时遇到了问题：

SELECT 
       CategoryID, Title as CategoryTitle,Description, 
       LastProductTitle,LastProductAddedDate
FROM
(
    SELECT
        C.CategoryID, C.Title,C.Description, C.Section,
        P.Title as LastProductTitle, P.AddedDate as LastProductAddedDate,                                          
        ROW_NUMBER() OVER (PARTITION BY P.CategoryID ORDER BY P.AddedDate DESC) AS Num

    FROM 
         Categories C         
         LEFT JOIN Products P ON P.CategoryID = C.CategoryID
) OuterSelect

WHERE 
     OuterSelect.Num = 1

换句话说:我想返回所有类别(从类别表)和标题和产品的添加日期(从Products表)，这是最后添加到这个类别。

如何使用实体框架查询来实现这一点？

以最有效的方式。

Answer 1

这种情况下是否需要Row_Number()？

下面返回Northwind数据库中所有客户的最新订单：

from c in Customers
join o in Orders on c.CustomerID equals o.CustomerID
group o by  new { c.CustomerID, c.ContactName}  into g
select new {
 CustomerID = g.Key.CustomerID,
 Name = g.Key.ContactName,
 OrderDate = g.Max(a=> a.OrderDate)
}

(从LinqPad创建的)查询是

SELECT MAX([t1].[OrderDate]) AS [OrderDate], [t0].[CustomerID], [t0].[ContactName] AS [Name]
FROM [Customers] AS [t0]
INNER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
GROUP BY [t0].[CustomerID], [t0].[ContactName]

这个代码执行的是左连接

from c in Customers
         join o in Orders on c.CustomerID equals o.CustomerID into g
         from a in g.DefaultIfEmpty()
         group a by new { c.CustomerID, c.ContactName}  into g
         select new
         {
             g.Key.CustomerID,
             g.Key.ContactName,
             RecentOrder  = g.Max(a=> a.OrderDate) 
         }

生成的查询是

SELECT MAX([t1].[OrderDate]) AS [RecentOrder], [t0].[CustomerID], [t0].[ContactName]
FROM [Customers] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
GROUP BY [t0].[CustomerID], [t0].[ContactName]