Search code examples
c#linqsql-order-byparent

Linq order by parent and child

I have two tables: Feedback and Comments. A feedback can have many comments. Basically a simple parent child relationship.

I have a page to list all feedback and the related comments like this:

Feedback A
Comment A1
Comment A2

Feedback B
Comment B1

Feedback C (note: no comments)

Each feedback and comments have a created date. At the moment I order by feedback date and then comment date so I always have the newest feedback in the top and then all comments after that.

What I'm trying to achieve is: when a new comment is added to a feedback this feedback should be displayed in the top, no matter how old the feedback is or if a feedback is added without comments this feedback should now be the first item in the list. Let's say a comment is added to Feedback B and after that a new feedback with no comments is added, then my list would look like this:

Feedback D (this is the new feedback)

Feedback B
Comment B1
Comment B2 (this is the new comment)

Feedback A
Comment A1
Comment A2

Feedback C (note: no comments)

Now Feedback D would be in the top of the list because it has the newest date of all feedback and comments and Feedback B would be second as it has Comment B2 which would have the second newest date.

This is my code so far:

_repositories.Feedback
.Include(f => f.Comments)
.OrderByDescending(f => f.PublishedDate);

What I would like is to modify 

.OrderByDescending(f => f.PublishedDate)
to get the correct order. Is that even possible?

Solution

  • Select last comment date for every Feedback and sort by them:

    _repositories.Feedback
.Include(f => f.Comments)
.OrderByDescending(f => 
    f.Comments
    .Select(c => (DateTime?)c.PublishedDate)
    .OrderByDescending(c => c)
    .FirstOrDefault() ?? f.PublishedDate
)
.ThenByDescending(f => f.PublishedDate);