Getting data of three levels of hierarchy

Question

I want to develop a discussions application where there are a number of Forums. In each forum there can be many Threads. And in each thread, there can be a number of Posts. In SQL, I have the following four tables:

FORUMS

• Id - int identity
• Name - nvarchar //name of forum

THREADS

• Id - int identity
• ForumId - int foreign key to FORUMS.Id
• Subject - nvarchar

POSTS

• Id - int identity
• ThreadId - int foreign key to THREADS.Id
• Submitter - int foreign key to USERS.Id
• SubmitterOn - datetime
• Content - nvarchar

USERS

• Id - int identity
• Name - nvarchar

What is the best way of returning a list with the following columns of all the forums in the database (one row in the list corresponds to one row in FORUMS):

• Forum - FORUMS.Name
• No of Threads - total number of rows in THREADS in Forum
• No of Posts - total number of rows in POSTS belonging to Forum
• Last Thread - THREADS.Subject of the most recent POSTS belonging to Forum
• Last Post Submitter - USERS.Name of most recent POSTS belonging to Forum
• Last Post Submitted time - POSTS.SubmittedOn of most recent POSTS belonging to Forum

Thanks

Answer 1

Something like:

var query = from f in context.Forums
            select new
            {
                Name = f.Name,
                Threads = f.Threads.Count(),
                Posts = f.Threads.Sum(t => t.Posts.Count()),
                LastThread = f.Threads.OrderByDescending(t => t.Id).First().Subject,
                LastPostSubmitter = f.Threads.SelectMany(t => t.Posts).OrderByDescending(p => p.SubmitterOn).First().Name,
                LastPostSubmitterTime = f.Threads.SelectMany(t => t.Posts).OrderByDescending(p => p.SubmitterOn).First().SubmitterOn
            }