OrderBy GroupBy using Linq

Question

I have an issue with my linq query. There are two tables, Thread and Post. It is built like a forum.

public class Post
{
    public int PostID { get; set; }
    public int ThreadID { get; set; }
    public DateTime Time { get; set; }

    public virtual Thread Thread { get; set; }
}

public class Thread
{
    public int ThreadID { get; set; }
    public string Title { get; set; }

    public virtual ICollection<Post> Posts { get; set; }
}

Threads:
"1, Thread A"
"2, Thread B"

Posts:
"1, 1, 01.01.15"
"2, 1, 02.01.15"
"3, 1, 03.01.15"
"4, 2, 01.01.15"

Expected result:
"3, 1, 03.01.15"
"4, 2, 01.01.15"

I would like to get all posts grouped by ThreadID, but I want it to be sorted by the latest post using Time.

This is my query.

Posts.OrderByDescending(x => x.Time).GroupBy(x => x.ThreadID).Select(x => x.FirstOrDefault());

This return distinct posts, but it finds the oldest post, not the newest.

If I use this code.

Posts.OrderByDescending(x => x.Time)

I get the sorting corerct, but not the distinct posts.

What am I missing?

First of all, people who down vote, please comment on why you down vote. Second, this does not solve the issue. Now I get the list grouped correctly, and it also looks lite it is sorted correctly, but the post that is returned for each group is the oldest, not the newest. — Martin at Mennt, Jan 24 '15 at 00:30
Post your defintions of *Thread* and *Post* classes , create a list ( *Posts* ) and fill with sample data, show your code about how you use it (this is OK) and ask the expected output. This is how a good question can be. I good question can be answered by copying and pasting it into our VS and working on it. — I4V, Jan 24 '15 at 00:37
Ok, question updated. Please remove down votes if the question is fulfilling your requirements. — Martin at Mennt, Jan 24 '15 at 00:40
@Martin OK it is an improvement but writing `Threads: "1, Thread A"` does solve anything. How can I write a code to test it. Should I do it by myself just because you are lazy to prepare a good question. Post a sample list (written in c# filling the *Posts* list) — I4V, Jan 24 '15 at 00:43
@I4V: First time I came a cross this level of detail. No one threatens you into answering my question, and I find your critique quite unreasonable. I see that there are other constructive answers comming in, and I will focus on them. Have a nice day. — Martin at Mennt, Jan 24 '15 at 00:47
@Martin good day, I am hoping at least, you have learnt what to do when asking a good question. — I4V, Jan 24 '15 at 00:50
@I4V: Nice, you're even running an organization now. I will keep it in mind for later. But I still think it is a bit harsh to keep the down vote even after i edited my post. It was not that badly presented. — Martin at Mennt, Jan 25 '15 at 00:50

Matt Johnson-Pint · Accepted Answer · 2015-01-24T00:59:03.983

1

If you want the latest post in each thread, try this:

Posts.GroupBy(x => x.ThreadID)
     .Select(x => x.OrderByDescending(y => y.Time).FirstOrDefault())
     .OrderByDescending(x => x.Time);

If you want all posts, grouped by thread, in the order of the latest post within the thread, then try this:

Posts.OrderByDescending(x => x.Time)
     .GroupBy(x => x.ThreadID)
     .SelectMany(x => x.OrderByDescending(y => y.Time));

Two order-by statements are probably needed, as the first will sort the entire list by time, and the second will ensure the items are still sorted after grouping. The SelectMany will ensure you get all posts back, rather than just one per group.

Through testing, you may find that the second order-by might not be needed. It just depends on if the order is maintained during grouping or not. (I haven't checked).

edited Jan 24 '15 at 00:59

answered Jan 24 '15 at 00:37

Matt Johnson-Pint

230,703
74
448
575

Thank you for your answer. I would like to return the newest post in each group. So I testet your first query, and I got this error `The method 'First' can only be used as a final query operation. Consider using the method 'FirstOrDefault' in this instance instead.` Did I do something wrong? – Martin at Mennt Jan 24 '15 at 00:55
Sorry - I guess you're using Entity Framework then? Yes, go ahead and use `FirstOrDefault`. – Matt Johnson-Pint Jan 24 '15 at 00:58
Yes. I use EF. Okay, think I got it correct now, added `FirstOrDefault` and also a final `OrderByDescending`. Final code `GroupBy(x => x.ThreadID).Select(x => x.OrderByDescending(y => y.Time).FirstOrDefault()).OrderByDescending(x => x.Time)` – Martin at Mennt Jan 24 '15 at 01:00
Thank you for taking time to help me :) – Martin at Mennt Jan 24 '15 at 01:01
I guess that will work, as you only have one item from each group then. – Matt Johnson-Pint Jan 24 '15 at 01:01

OrderBy GroupBy using Linq

1 Answers1