linq multi left join to same property

Question

Is it possible to write a LINQ for this query? (in .NET 3.5)

select tt.id
     ,tt.detailscount
     ,ttdmx.detailorder
     ,ttdmx.detailtype
     ,ttdm1.detailorder
     ,ttdm1.detailtype
     ,ttdm2.detailorder
     ,ttdm2.detailtype
from test.testtable tt
left join test.testtabledetails ttdmx on tt.id = ttdmx.causeid 
    and tt.maxcausedetailorder = ttdmx.detailorder
left join test.testtabledetails ttdm1 on tt.id = ttdm1.causeid 
    and tt.maxcausedetailorder - 1 = ttdm1.detailorder
left join test.testtabledetails ttdm2 on tt.id = ttdm2.causeid 
    and tt.maxcausedetailorder - 2 = ttdm2.detailorder

The classes are something like this:

public class Test {
  public int id {get;set;}
  public IList<TestDetails> details {get;set}
}

and

public class TestDetails {
  public int id {get;set;}
  public int detailOrder {get;set;}
  public int detailType {get;set;}
}

I tried with queryOver but it can't join to the same property twice.

Answer 1

The easiest way would be to write:

...
from ttdmx in db.testtabledetails.Where(x => 
    tt.id == x.causeid && 
    tt.maxcausedetailorder == x.detailorder).DefaultIfEmpty()
...

This will be translated into a SQL left join