Outer join in LINQ Problem

Question

Id like to perform an outer join with the second join statement in this query, I keep getting weird errors! (it must be the 3rd RedBull)

var Objeto = from t in Table1.All()
             join su in table2.All() on t.Id equals su.Id
             join tab2 in Table1.All() on t.PId equals tab2.Id //<-I want it here
             select new
             {
                 t.Field1,
                 SN = su.Field123,
                 PTN = tab2.FieldABC
              };

Any help would be appreciated.

[Edit] - I neglected to say that I'm using SubSonic 3.0, the bug seems to be with SubSonic.....

Answer 1

See sample here

Answer 2

Performing an outer join requires two steps:

1. Convert the join into a group join with into
2. Use DefaultIfEmpty() on the group to generate the null value you expect if the joined result set is empty.

You will also need to add a null check to your select.

var Objeto = from t in Table1.All()
             join su in table2.All() on t.Id equals su.Id
             join tab2 in Table1.All() on t.PId equals tab2.Id into gj
             from j in gj.DefaultIfEmpty()
             select new
             {
                 t.Field1,
                 SN = su.Field123,
                 PTN = (j == null ? null : j.FieldABC)
              };

Answer 3

Any progress on this problem? I'm experiencing it as well and have entered an issue into Subsonic's github project but I'm the only one who has commented on it. :)