How to run Linq with order by and group by

Question

I have three tables in DB

1. VoucherQuestionCollection
2. Post
3. PostComment (Contains the multiple comments of Post table)

The relation between tables is

VoucherQuestionCollection.discussionid = Post.PostId
and Post.PostId = PostComment.PostId

VoucherQuestionCollection table have one column named "WonDate" which contains datetime values. What i need is, i need to fetch the data from VoucherQuestionCollection table based on following conditions:-

1. VoucherQuestionsCollection's WonDate that should be the least date (say the one which going to expire first)
2. PostComment's comment count should also be the least count. (that is the posts with lowest comment)

So how do i build the query in Linq to produce the desired output by considering above conditions?

This is what i did so far, but stuck with how to proceed further.

 var voucherQuestionTotalCollection = (from voucherQuestionCollection in this.GetDbSet<Jimble.Model.VoucherQuestionCollection>()
                                                  join posts in this.GetDbSet<Jimble.Model.Post>() on voucherQuestionCollection.DiscussionId equals posts.PostId
                                                  join comm in this.GetDbSet<Jimble.Model.PostComment>() on posts.PostId equals comm.PostId into gj
                                                  from sub in gj.GroupBy(c => c.PostId).Select(g => new { Available = g.Count() }).DefaultIfEmpty()
                                                  where voucherQuestionCollection.UserId != userId && voucherQuestionCollection.VoucherWonDate > DateTime.Now.AddDays(-7)
                                                  orderby voucherQuestionCollection.VoucherWonDate descending
                                                  select new { Id = voucherQuestionCollection .Id,
                                                               VoucherWonDate = voucherQuestionCollection.VoucherWonDate,
                                                               DiscussionId = voucherQuestionCollection.DiscussionId,
                                                               TotCommentCount = sub.Available
                                                  }).OrderBy(c=>c.TotCommentCount);

Sample table

VoucherQuestionCollection                    Post                    PostComment
Id  DiscussionId   WonDate                   Id     PostText         Id   PostId   CommentText

1   1              2014-11-21 17:13:00.113   1      FirstPost        1    1        CommentText1
2   2              2014-11-22 17:13:00.113   2      SecondPost       2    1        CommentText2
3   3              2014-11-23 17:13:00.113   3      ThridPost        3    2        CommentText3
4   4              2014-11-24 17:13:00.113   4      FourthPost       4    2        CommentText4
                                                                     5    2        CommentText5
                                                                     6    3        CommentText6

This is the output i need If the table status is like i shown above, the expected output is VoucherCollectionTable's 3rd entry since it has only one count in comment table. If the PostComment table have equal comments i need to get the entry of 1 from VoucherCollectionTable since the least date is 2014-11-21 17:13:00.113

NOTE: The VoucherQuestionCollection will have multiple entries i need to fetch only six records based on this above condition.

Having trouble to understand my explanation above, simply this is the SQl Query

Select * from VoucherQuestionCollections a 
inner join Posts b on a.DiscussionId = b.PostId
Left join (Select PostComments.PostId,COUNT(PostComments.PostId) as tot from PostComments Group    by PostComments.PostId)c on b.PostId = c.PostId
order by a.VoucherWonDate,c.tot

How to write this same in Linq

Answer 1

From the appearance of your database structure you don't need to group by. Just order by VoucherWonDate descending and then by the number of PostComments for the associated Post.

Try something like:

var voucherQuestionTotalCollection = 
this.GetDbSet<Model.VoucherQuestionCollection>()
.Where(p => p.UserId != userId )
.OrderByDescending(p => p.VoucherWonDate)
.ThenBy(p => p.Post.SelectMany(q => q.PostComments).Count());

I have included the where clause from your query which was not mentioned in the question.