LINQ self referencing query
- by Chris
I have the following SQL query:
select
p1.[id],
p1.[useraccountid],
p1.[subject],
p1.[message],
p1.[views],
p1.[parentid],
case
when p2.[created] is null then p1.[created]
else p2.[created]
end as LastUpdate
from forumposts p1
left join
(
select
parentid, max(created) as [created]
from
forumposts
group by
parentid
) p2 on p2.parentid = p1.id
where p1.[parentid] is null
order by LastUpdate desc
Using the following class:
public class ForumPost : PersistedObject
{
public int Views { get; set; }
public string Message { get; set; }
public string Subject { get; set; }
public ForumPost Parent { get; set; }
public UserAccount UserAccount { get; set; }
public IList<ForumPost> Replies { get; set; }
}
How would I replicate such a query in LINQ? I've tried several variations, but I seem unable to get the correct join syntax. Is this simply a case of a query that is too complicated for LINQ? Can it be done using nested queries some how?
The purpose of the query is to find the most recently updated posts i.e. replying to a post would bump it to the top of the list. Replies are defined by the ParentID column, which is self-referencing.
