CODEWITHSUNDEEP
c#linqsubsonic3
Hector Minaya
Hector Minaya

Reputation: 1705

Outer join in LINQ Problem

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.....

Upvotes: 2

Views: 919

Answers (1)

dahlbyk
dahlbyk

Reputation: 77520

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)
              };

Upvotes: 4

Related Questions