Linq to SQL filtered association?

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?

13.10.2009 19:41:08

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

17.10.2009 14:20:15
As JustLoren said, your Linq query is incorrect. I think the JustLoren's query is what you might need.
MNZ 13.10.2009 21:11:09
I have: join comment in context.Comments on topic.ForumTopicId equals comment.TargetId into topicComments in there, that is the relationship. And I tried the new ForumTopic {} thing but Linq told me it was forbidden, probably becuase ForumTopic is a type Linq to SLQ has mapped in my mapping.xml. Any other ideas?
Bryan 14.10.2009 18:44:31
@Bryan: I was assuming you are using the L2S designer. I'm not familiar with the mapping.xml implementation. When I meant relationship, I meant in your schema not in your select statement. One of the huge benefits of Linq is the way it manages relationships of objects and allows you to query against them. I'm not sure why your compiler was complaining about it being forbidden - there's numerous examples on the web where the L2S type is instantiated and filled explicitly as part of the select command. Does your mapping.xml have a mapping defined for LastComment? If so, what is it?
JustLoren 14.10.2009 19:25:17
<Table Name="ForumTopics"> <Type Name="Objects.ForumTopic"> <Column Name="ForumTopicId" Member="ForumTopicId" IsPrimaryKey="true" CanBeNull="false" /> <Column Name="OwnerId" Member="OwnerId" CanBeNull="false" /> <Column Name="CategoryId" Member="CategoryId" CanBeNull="true" /> <Column Name="CreatedDate" Member="CreatedDate" CanBeNull="false" /> <Column Name="Topic" Member="Topic" CanBeNull="false" /> <Column Name="IsSticky" Member="IsSticky" CanBeNull="false" /> <Column Name="IsClosed" Member="IsClosed" CanBeNull="false" />
Bryan 16.10.2009 21:11:45
<Column Name="ViewCount" Member="ViewCount" CanBeNull="false" /> <Column Name="TotalComments" Member="TotalComments" IsDbGenerated="true" /> <Association Member="LastComment" OtherKey="TargetId" ThisKey="ForumTopicId" /> </Type> </Table> <Table Name="Comments"> <Type Name="Objects.Comment"> <Column Name="CommentId" Member="CommentId" IsPrimaryKey="true" CanBeNull="false" /> <Column Name="TargetId" Member="TargetId" CanBeNull="true" /> <Column Name="CreatedDate" Member="CreatedDate" CanBeNull="false" />
Bryan 16.10.2009 21:12:18