Help with linq orderby on class aggregate relationships

Question

Hi,

I am getting a little confused and need some help please. Take these two classes

public class Comment
{
     public string Message {get; set;}
     public DateTime Created {get; set;}
}

public class Post
{
    public int PostId {get; set;}
    public string Content {get; set;}
    public IList<Comment> Comments {get; set;}
}

I want to write a linq query which returns a single Post but ordered by the comment created date.

So i started off constructing my linq query as follows:

var query = from p in _repository.GetPosts()
                        where p.PostId == id
                        orderby p.Comments.Select(x => x.Created)
                        select p;

            return query.Single();

But the orderby statement seem not to work! It just returns my list in the default sort order. Any suggestions on how i can make this work??? Thanks in advance!

Answer 1

Ordered by which comment date? the first? the last? You cuold try:

orderby p.Comments.Max(x=>x.Created)

for example.

Also - your Single suggests you expect exactly one row, in which case there isn't much point sorting it. Do you mean First() ?

---

Or do you mean that you want to sort the Comments? In which case, get the Post first;

Post post = ...

Now... sorting the Comments is a little tricky because of your IList<T> - if you don't mind it being a little inefficient, this is simple:

post.Comments = post.Comments.OrderBy(x=>x.Created).ToList();

Of course, if the Comments was List<T>, you could do:

post.Comments.Sort((x, y) => (x.Created.CompareTo(y.Created)));

There are also tricks you can do to make an extension method of the form:

post.Comments.Sort(x=>x.Created);

i.e.

public static void Sort<TSource, TKey>(
    this List<TSource> source,
    Func<TSource, TKey> selector)
{
    var comparer = Comparer<TKey>.Default;
    source.Sort((x, y) => comparer.Compare(selector(x), selector(y)));
}

Answer 2

Your orderby projection is returning an IEnumerable<DateTime> - that sounds unlikely to be what you want.

A post has many comments - which one do you want to take as the one to use the created date of for ordering? My guess is the first:

var query = from p in _repository.GetPosts()
                    where p.PostId == id
                    orderby {
                        Comment comment = p.Comments.FirstOrDefault();
                        return comment == null ? DateTime.MinValue : comment.Created;
                    }
                    select p;

Answer 3

This will returns in an IEnumerable collection, which isn't a comparable value:

p.Comments.Select(x => x.Created)

Try this instead:

p.Comments.Max(x => x.Created)

Which returns the most recent comment's date

Answer 4

If you want to sort the resulting list of comments you can do it after fetching the post by doing:

p.Comments = p.Comments.OrderBy(x => x.Created).ToList();

.

Answer 5

The problem is you're trying to sort the list of posts by a list of comment created dates, not sort the list of comments.

If I've read your question correctly, I assume you want to get the single post out, then order the comments within that post. Try the following to do that:

var query = from p in _repository.GetPosts()
            where p.PostId == id
            orderby p.Comments.Select(x => x.Created)
            select p;

var ret = query.Single();
ret.Comments = ret.Comments.OrderBy(x => x.Created).ToList();
return ret;

Answer 6

wow this site is simply awesome. The replies were super fast and helpful! THanks very much guys!!!!