How to create view model without sorting collections in memory

I have a view model (below).

public class TopicsViewModel
{
    public Topic Topic { get; set; }
    public Reply LastReply { get; set; }
}

I want to populate an IQueryable<TopicsViewModel> with values from my IQueryable<Topic> collection and IQueryable<Reply> collection. I do not want to use the attached entity collection (i.e. Topic.Replies) because I only want the last reply for that topic and doing Topic.Replies.Last() loads the entire entity collection in memory and then grabs the last one in the list. I am trying to stay in IQueryable so that the query is executed in the database.

I also don't want to foreach through topics and query replyRepository.Replies because looping through IQueryable<Topic> will start the lazy loading. I'd prefer to build one expression and have all the leg work done in the lower layers.

I have the following:

IQueryable<TopicsViewModel> topicsViewModel = from x in topicRepository.Topics
                                              from y in replyRepository.Replies
                                              where y.TopicID == x.TopicID
                                              orderby y.PostedDate ascending
                                              select new TopicsViewModel { Topic = x, LastReply = y };

But this isn't working. Any ideas how I can populate an IQueryable or IEnumerable of TopicsViewModel so that it queries the database and grabs topics and that topic's la开发者_C百科st reply? I am trying really hard to avoid grabbing all replies related to that topic. I only want to grab the last reply.

Thank you for any insight you have to offer.

---

Since nobody is answering I am going with a foreach solution for now. I figure foreaching through the topics that are eventually going to be lazy loaded anyway is far better than populating a collection of replies just so I can access the last object in the collection.

Here is what I did for now:

                List<TopicsViewModel> topicsViewModelList = new List<TopicsViewModel>();
                foreach (Topic topic in topics)
                {
                    Reply lastReply = replyRepository.GetRepliesBy_TopicID(topic.TopicID).OrderBy(x => x.PostedDate).LastOrDefault();

                    topicsViewModelList.Add(new TopicsViewModel
                    {
                        Topic = topic,
                        LastReply = lastReply
                    });
                }

I'm just loading my IQueryable<Topics> first, then looping through the final results (so as to ensure that proper paging of the data is done before looping) and loading in the last reply. This seems to avoid ever populating a collection of replies and instead grabs only the last reply for each topic.

---

        from r in replies
        group r by new { r.TopicId } into g
        select new
            {
                TopicId = g.Key.TopicId, 
                LastReply = g.Max(p => p.PostedDate)
            }