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.
