sql - Linq to sql - 加入 2 个表，从右表中选择 1 行，具有 1 对多关系

Question

我正在尝试选择论坛类别和每个类别中的最后一篇文章。我已经能够使用 OUTER APPLY 在 SQL 中完成此操作，但无法成功将其转换为 LINQ。

在 SQL 中产生所需的结果:

SELECT  fc.CategoryID, fc.CategoryName, fc.PostCount, ft.Title, ft.LastPost, ft.LastPostId, ft.TopicId
FROM    ForumCategory AS fc
OUTER APPLY
    (
    SELECT TOP (1) *
    FROM ForumTopic
    WHERE ForumTopic.CategoryID = fc.CategoryID
    ORDER BY ForumTopic.lastpost DESC
    ) ft

我尝试转换为 LINQ:

Dim query = From fc In ctx.ForumCategories _
            Join ft In ctx.ForumTopics On fc.CategoryID Equals ft.CategoryID _
            Select New With {
                                fc.CategoryID, 
                                fc.CategoryName, 
                                fc.PostCount, 
                                ft.Title, 
                                ft.LastPostId, 
                                ft.LastPost.OrderByDescending().First()
                            }

我收到一条错误消息:“OrderByDescending”不是“Date”的成员？

Answer 1

你写成这样:

var q = from fc in ctx.ForumCategories
        from ft in (from x in ctx.ForumTopic
                    where x.CategoryID == fc.CategoryID
                    order by x.lastpost desc
                    select new
                    {
                       x.Title, 
                       x.LastPost, 
                       x.LastPostId, 
                       x.TopicId
                    }).Take(1).DefaultIfEmpty()
        select new
        {
           fc.CategoryID, 
           fc.CategoryName, 
           fc.PostCount, 
           ft.Title, 
           ft.LastPost, 
           ft.LastPostId, 
           ft.TopicId
        };