LINQ for an SQL with multiple LEFT OUTER JOINS on different tables

Question

I am trying to create a LINQ query containing the LEFT outer joins. There are certain examples but I have bit different scenario

Select * from A_TABLE
LEFT OUTER JOIN B_TABLE ON A_TABLE.Id = B_TABLE.A_TABLE_Id
LEFT OUTER JOIN C_TABLE ON B_TABLE.Id = C_TABLE.B_TABLE_Id

Second join is not on A_TABLE it is B_TABLE and C_TABLE

can we convert it to LINQ?

Answer 1

You can simply convert your query to linq query like this:

var results = (from a in A_TABLE
               join b in B_TABLE
               on a.A_TABLE_Id equals b.A_TABLE_Id into ab
               from b in ab.DefaultIfEmpty()
               join c in C_TABLE_List on b.B_TABLE_Id equals c.B_TABLE_Id

               select new
                   {
                     ClassAProperty1 = a.Property1,
                     ClassBProperty1 = b.Property1,
                     ClassCProperty1 = c.Property1
                   }).ToList();

you can continue on joining tables whatever times you need.

Don't forget to change Property# to required property names.

for more info take a look at LINQ Left Outer Join, and this stack thread.

Update: this is the Lambda expressions version of the query:

var result = A_TABLE
             .GroupJoin(B_TABLE,
                 a => a.A_TABLE_Id,
                 b => b.A_TABLE_Id,
                 (a, b) =>
                 new {
                       tableAProperty1 = a.Property1,
                       tableAProperty2 = a.Property2, /* You cannot access this property in next join if you don't add it here */
                       B_TABLE = b.FirstOrDefault() /* This is the way to access B_TABLE in next join */
                 }).GroupJoin(C_TABLE,
                       ab => ab.B_TABLE.B_TABLE_Id,
                       c => c.B_TABLE_Id,
                       (ab, c) =>
                       new {
                             ab.tableAProperty1,
                             B_TABLEProperty2 = ab.B_TABLE.Property2,
                             C_TABLE = c.FirstOrDefault()
                            }).ToList();