SQL's Rownumber with Linq-to-entities

Question

I am converting my project to use EF and also want to covert stored procedures into Linq-to-entities queries.

This my SQL query (simple version) that I have trouble to convert:

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

In words: I want to return all Categories (from Categories table) and title and date of addition of the product (from Products table) that was added last to this category.

How can I achieve this using Entity frame work query?
In most efficient way.

Answer 1

Is a Row_Number() needed in this case?

The following returns the most recent order for all customers in the Northwind database:

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)
}

The query created (from LinqPad) is

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]

This one does a left join instead

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) 
         }

and the query generated is

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]