Need help ordering Linq result
I have Linq code that works great. It retrieves a list of messages sorted by the last word.
The trap here is order ... he orders the last COMMENTS. So if a message on a board just got a comment, it will be at the top of the list (i.e. the most recent).
kewl ... but what about the new posts you just created? They are listed at the bottom because they have no comments: (As if I want to say "order by most recent comments ... but if you have no comments, then create a date by the date your post was created").
here is my linq ...
boardPostList = (from bp in db.tblBoardPosts.Where(whereClause)
orderby (from bc in bp.tblBoardComments
orderby bc.DateModified descending
select bc.DateModified).First() descending
select bp).ToPagedList(pageNumber, numberOfResults);
Does anyone have any suggestions?
a source to share
Is there a reason why you cannot update a field tblBoardPosts
whenever a comment is posted to it? Save the "posted or last comment" date, then you have a much simpler query, and one that doesn't need to check every comment in the system to determine what to do.
Having said that, this query might work if your field DateModified
is NULL:
boardPostList = (from bp in db.tblBoardPosts.Where(whereClause)
orderby ((from bc in bp.tblBoardComments
orderby bc.DateModified descending
select bc.DateModified).FirstOrDefault() ?? bp.DateModified) descending
select bp).ToPagedList(pageNumber, numberOfResults);
If it's just a straight column DateTime
, the result FirstOrDefault
is still there DateTime
, which won't be null ... You can try:
boardPostList = (from bp in db.tblBoardPosts.Where(whereClause)
let lastComment = bp.tblBoardComments
.OrderByDescending(bc => bc.DateModified)
.FirstOrDefault()
let lastModified = (lastComment == null
? bp.DateModified
: lastComment.DateModified)
orderby lastModified descending
select bp).ToPagedList(pageNumber, numberOfResults);
This is pretty disgusting, but may not translate to SQL. I would of course try to go for a schema where the post itself tracks it.
a source to share