Linq to SQL filtered association?

Question

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?

JustLoren JustLoren · Accepted Answer · 2009-10-13T19:50:56

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

Linq to SQL filtered association?

1 Answers