Linq to SQL filtered association??

Question

Hi - I have a object:

public class ForumTopic
{
        public Guid ForumTopicId { get; set; }
        public Guid OwnerId { get; set; }
        public Guid CategoryId { get; set; }
        public DateTime CreatedDate { get; set; }
        public string Topic { get; set; }
        public bool IsSticky { get; set; }
        public bool IsClosed { get; set; }
        public int ViewCount { get; set; }
        public int TotalComments { get; set; }
        public Comment LastComment { get; set; }
}

---

I then have a Linq query and I need to figure out how to populate the LastComment and I can't create a new ForumTopic becuase Linq tells me that is breaking the rules...

IQueryable<ForumTopic> query = from topic in context.ForumTopics

                               join comment in context.Comments on topic.ForumTopicId equals comment.TargetId into topicComments
                               from lastComment in topicComments.DefaultIfEmpty().OrderByDescending(c => c.CreatedDate).Take(1)

                               orderby topic.IsSticky, topic.CreatedDate descending
                               select topic;

The query returns everything correct in SQL, however topic.LastComment is null.

Any ideas?

Answer 1

The main problem is you're not assigning the LastComment. Without a relationship established in the database, it has no idea how to fill that object.

You're going to need to manually assign the comment:

IQueryable<ForumTopic> query = from topic in context.ForumTopics

orderby topic.IsSticky, topic.CreatedDate descending
select new ForumTopic 
{
  ForumTopicId = topic.ForumTopicId,
  OwnerId = topic.OwnerId,
  // .. etc
  LastComment = topic.Comments.OrderByDescending(c => c.CreatedDate).FirstOrDefault();
};

Obviously, I'm assuming you have a parent-child relationship between topic and comments. If you don't, you should reconsider how you're using linq :p