Correct syntax for multiple left joins in LINQ?

Question

I am trying to figure out the LINQ syntax for multiple left join, but I am getting the error: The name 'c' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals'.

I have already tried swapping, and if I do, it makes both 'c' and 'd' have the "not in scope" error.

            var result = 
                    from a in db.tableA

                    join b in db.tableB //first join (inner join)
                        on a.field1 equals b.field1

                    join c in db.tableC //second join (left join)
                        on a.field1 equals c.field1
                            into left_one

                    join d in db.tableD   //third join (left join)
                        on c.field2 equals d.field2
                        // ^ here
                            into left_two


                    where a.field1 == theValueImSearchingFor


                    from c in left_one.DefaultIfEmpty()
                    from d in left_two.DefaultIfEmpty()

                    select new CombinedObject()
                    {
                        ...
                    }

The reason I am using on c.field2 equals d.field2 in the third join statement is that my tables are structured like this:

tableA:   field1
tableB:   field1
tableC:   field1    field2
tableD:             field2

That is, the only way to relate tableD to the rest of the data is to use field2.

Can someone please correct my syntax? Or is there a certain way I have to do it given my setup of tables?

Answer 1

I use this type of syntax:

var results = (from a in db.tableA
  from b in db.tableB.Where(s => s.field1 == a.field1)
  from c in db.tableC.Where(s => s.field1 == a.field1).DefaultIfEmpty()
  from d in db.tableD.Where(s => s.field2 == c.field2).DefaultIfEmpty()
  select new CombinedObject() { });

It seems to work well on multiple tables. I think I got my field1s and field2s right to match your example :)

[Edit]

As per the comment, if you want to add in some additional filtering, you just add it in where appropriate into the Where(). Eg:

  from c in db.tableC.Where(s => s.field1 == a.field1 && s.field3 == someVariable).DefaultIfEmpty()

Something like that :)