LINQ query for a forum

Question

Hi,

I'm coding this forum and since I'm new to LINQ I ran into this problem when the user hits the main page. I want a table displaying a list of forums like this:

Forum  --- Topics (count) --- Posts (count) --- LastPostUserId --- LastPostTime

I have the following SQL tables:

Forums:
ForumId (int32),
Title (string),
Description (string)

ForumThreads:
ThreadId (int32),
ForumId (int32),
UserId (guid),
Subject (string),
Views (int32),
CreateDate (DateTime)

ForumPosts:
PostId (int32),
ThreadId (int32),
UserId (guid),
Post (string),
CreateDate (datetime)

Thank you...

Answer 1

from forum in forums
from posts in db.ForumPosts.Where(p => p.Thread.ForumId.Equals(forum.ForumId))
select new
{
Forum = forum.Title, 
Topics = forum.ForumThreads.Count(),
Posts = posts.Count(),
LastPostBy = posts.OrderByDescending(p => p.CreateDate).FirstOrDefault(p => p.UserId),
LastPostTime= posts.Max(p => p.CreateDate))
}

untested ofcourse, but try to start from here and check the SQL query(s) it executes and let me know if it needs optimizing.

Answer 2

This almost does the trick (although it generates some horrible SQL ;-)...)

from forum in Forums
let posts = ForumPosts.Where(p => p.ForumThreads.ForumId.Equals(forum.ForumId))
select new
{
    Forum = forum.Title,
    Description = forum.Description,
    Topics = forum.ForumThreads.Count(),
    Posts = posts.Count(),
    LastPostId = posts.OrderByDescending(p=>p.PostId).Take(1).Select(p=>p.PostId),
    LastPostThreadId = posts.OrderByDescending(p=>p.PostId).Take(1).Select(p=>p.ThreadId),
    LastPostUserId = posts.OrderByDescending(p=>p.PostId).Take(1).Select(p=>p.UserId),
    LastPostTime = posts.OrderByDescending(p=>p.PostId).Take(1).Select(p=>p.CreateDate)
}

Last thing - I have a relationship from SQL table 'ForumPosts' to 'Aspnet_Users' and I would like to display the column Aspnet_Users.UserName as LastPostUserName... how can that be done? And how would you optimize the whole query?

Answer 3

For displaying the user's name if you use membership and you don't want to include the aspnet_Users in your dbml:

...
LastPostUserId = posts.OrderByDescending(p=>p.PostId).Take(1).Select(p=> Membership.GetUser(p.UserId))
...

Another change to make your posted sample a bit better is to add the orderbydescending in the posts variable: Then you can drop the 4 times repeated OrderByDescending from the select clause:

from forum in Forums
let posts = ForumPosts.Where(p => p.ForumThreads.ForumId.Equals(forum.ForumId)).OrderByDescending(p=>p.PostId)
select new
{
    Forum = forum.Title,
    Description = forum.Description,
    Topics = forum.ForumThreads.Count(),
    Posts = posts.Count(),
    LastPostId = posts.Take(1).Select(p=>p.PostId),
    LastPostThreadId = posts.Take(1).Select(p=>p.ThreadId),
    LastPostUserId = posts.Take(1).Select(p=>p.UserId),
    LastPostTime = posts.Take(1).Select(p=>p.CreateDate)
}

Or even cleaner:

from forum in Forums
let posts = ForumPosts.Where(p => p.ForumThreads.ForumId.Equals(forum.ForumId))
let lastPost = posts.OrderByDescending(p=>p.PostId).Take(1)
select new
{
    Forum = forum.Title,
    Description = forum.Description,
    Topics = forum.ForumThreads.Count(),
    Posts = posts.Count(),
    LastPostId = lastPost.PostId,
    LastPostThreadId = lastPost.ThreadId,
    LastPostUserId = lastPost.UserId,
    LastPostUserName = Membership.GetUser(lastPost.UserId),
    LastPostTime = lastPost.CreateDate
}

Test this code when there are no last posts tho, I think it might throw an error if Take(1) is null..