CODEWITHSUNDEEP
sqllinqlinq-to-sql
Brian Salta
Brian Salta

Reputation: 1576

Linq to sql - Join 2 tables, select 1 row from right table with a 1 to many relationship

I am trying to select the forum categories and the last post in each respective category. I have been able to accomplish this in SQL using OUTER APPLY, but haven't been able to successfully convert this to LINQ.

Produces the desired result in 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

My attempt at converting to 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()
                            }

I am getting an error stating: 'OrderByDescending' is not a member of 'Date?'

Upvotes: 2

Views: 1303

Answers (1)

Magnus
Magnus

Reputation: 46909

You write it as follows:

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

Upvotes: 3

Related Questions