LINQ self referencing query

I have the following SQL query:

select
    p1.[id], 
    p1.[useraccountid], 
    p1.[subject], 
    p1.[mess开发者_如何学Goage], 
    p1.[views], 
    p1.[parentid], 
    max(
        case
            when p2.[created] is null then p1.[created]
            else p2.[created]
        end
    ) as LastUpdate
from forumposts p1
    left join 
    (
        select 
            id, parentid, created
        from
            forumposts 
    ) p2 on p2.parentid = p1.id 
where 
    p1.[parentid] is null
group by 
    p1.[id], 
    p1.[useraccountid], 
    p1.[subject], 
    p1.[message], 
    p1.[views], 
    p1.[parentid]
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.

The syntaxt of left join in LINQ is :

(i put it in VB.NET) :

Dim query = From table1 in myTable.AsEnumarable 'Can be a collection of your object
            Group join table2 in MyOtherTable.AsEnumerable 
            On table1.Field(Of Type)("myfield") Equals table2.Field(Of Type)("myfield")
            In temp
            From table2 in temp.DefaultIsEmpty()
            Where table1.Field(Of Type)("Myanotherfield") is Nothing 'exemple
            Select New With { .firstField = table1.Field(Of Type)("Myanotherfield")
                              .secondField = table2.Field(Of Type)("Myanotherfield2")}

Something like that

Ju

I discovered that NHibernate LINQ support doesn't include joins. That, coupled with an apparent inexperience with complex LINQ queries, I resorted to the following work around:

• Add a Modified column to the posts table.
• On reply, update parent's Modified column to match reply's Created column
• Sort by and retrieve the value of the Modified column for post display.

I think it's a pretty clean work around, given the limitations of the code. I dreadfully wanted to avoid having to resort to adding another entity, referencing a view, or using a stored procedure + data table combination for this particular piece of code only. Wanted to keep everything within the entities and use NHibernate only, and this fix allows that to happen with minimal code smell.

Leaving this here to mark as answer later.