title: Linq left join returns inner join
body: <p>I am trying to perform an outer join in C# using Linq, the person mentoring me keeps saying I shouldn't try to do an outer join which isn't really an answer.</p>
  
  <p>What I got from the other threads is that I need the <code>.DefaultIfEmpty()</code> where ever I may not have a record.</p>
  
  <p>I tried it first on just the lines where there may be a missing information then added it to every line just to see if that was the problem.</p>
  
  <p>Every time I run this I get only the inner join records. It works great other than it is not including the two records from my DB that only have information in the first two tables.</p>
  
  <pre><code>var sqlQuery =
  from s in ctx.Suppliers
  from sp in ctx.SupplierParts
      .Where(sp =&gt; sp.SupplierID == s.SupplierID)
      .DefaultIfEmpty()
  from sm in ctx.SupplierManufacturerRelations
      .Where(sm =&gt; sm.SupplierPNID == sp.SupplierPNID)
      .DefaultIfEmpty()
  from mp in ctx.ManufacturerParts
      .Where(mp =&gt; mp.MfgPNID.Equals(sm.MfgPNID))
      .DefaultIfEmpty()
  from m in ctx.Manufacturers
      .Where(m =&gt; m.ManufacturerID.Equals(mp.ManufacturerID))
      .DefaultIfEmpty()
  from im in ctx.ItemMasters
      .Where(im =&gt; im.PreID == mp.PreID)
      .Where(im =&gt; im.PartNumber == mp.PartNumber)
      .DefaultIfEmpty()
  from c in ctx.ComponentClasses
      .Where(c =&gt; c.CCID == im.CCID)
      .DefaultIfEmpty()
  from um in ctx.UnitsOfMeasures
      .Where(um =&gt; um.UOMID == sp.UOMID)
      .DefaultIfEmpty()
  
  select new
  { my variables}
  
  var querylist = sqlQuery.Where(n =&gt; n.SupplierID == thisSupplier).ToList();
  </code></pre>
  
  <p>I also tried</p>
  
  <pre><code>from s in ctx.Suppliers
      join sp in ctx.SupplierParts on s.SupplierID equals sp.SupplierID
      join sm in ctx.SupplierManufacturerRelations on sp.SupplierPNID equals sm.SupplierPNID into spartgroup
  from sm in spartgroup.DefaultIfEmpty()
      join mp in ctx.ManufacturerParts on sm.MfgPNID equals mp.MfgPNID into mpartgroup
  from mp in mpartgroup.DefaultIfEmpty()
       join m in ctx.Manufacturers on mp.ManufacturerID equals m.ManufacturerID into mgroup
  from m in mgroup.DefaultIfEmpty()
       join im in ctx.ItemMasters 
       on new { key1 = (int)mp.PreID, key2 = (int)mp.PartNumber }
       equals new { key1 = im.PreID, key2 = im.PartNumber }
       into tpartgroup
  from im in tpartgroup.DefaultIfEmpty()
       join c in ctx.ComponentClasses on im.CCID equals c.CCID into fullgroup
  from c in fullgroup.DefaultIfEmpty()
       join um in ctx.UnitsOfMeasures on sp.UOMID equals um.UOMID
  </code></pre>
  
  <p>This SQL query works and doesn't omit the rows</p>
  
  <pre><code>    SELECT Supplier.SupplierID
           , SupplierPart.SupplierPNID
           , SupplierPart.SupplierPN
           , SupplierPart.Description
           , SupplierManufacturerRelation.MfgPNID
           , ManufacturerPart.PreID
           , ManufacturerPart.PartNumber
           , ItemMaster.CCID
           , ItemMaster.Description AS Expr1
        FROM Supplier  
  Inner JOIN SupplierPart 
          ON Supplier.SupplierID = SupplierPart.SupplierID 
   Left JOIN SupplierManufacturerRelation 
          ON SupplierPart.SupplierPNID = SupplierManufacturerRelation.SupplierPNID 
   Left JOIN ManufacturerPart 
          ON SupplierManufacturerRelation.MfgPNID = ManufacturerPart.MfgPNID 
   Left JOIN ItemMaster 
          ON ManufacturerPart.PreID = ItemMaster.PreID 
         AND ManufacturerPart.PartNumber = ItemMaster.PartNumber
       WHERE Supplier.SupplierID = 9
  </code></pre>