title: Left join query with EF 6
body: <p>First, here is the simplified EF model I'm using :</p>
  
  <pre><code>public partial class MaterielRoulant : IKeyed&lt;int&gt;
  {
      public MaterielRoulant()
      {
          this.RelationsMaterielRoulantEquipement = new List&lt;RelationMaterielRoulantEquipement&gt;();
      }
  
      public int Id
      {
          get;
          set;
      }
  
      public virtual ICollection&lt;RelationMaterielRoulantEquipement&gt; RelationsMaterielRoulantEquipement
      {
          get;
          private set;
      }
  }
  
  public partial class RelationMaterielRoulantEquipement : IKeyed&lt;int&gt;
  {
      public int EquipementId
      {
          get;
          set;
      }
  
      public int Id
      {
          get;
          set;
      }
  
      public int MaterielRoulantId
      {
          get;
          set;
      }
  
  }
  
  public partial class Equipement : IKeyed&lt;int&gt;, IEditable, IArchivable, IAuditable
  {
      public int Id
      {
          get;
          set;
      }
  }
  
  public partial class Filiation : IKeyed&lt;int&gt;
  {
      public int EquipementFilsId
      {
          get;
          set;
      }
  
      public int EquipementPereId
      {
          get;
          set;
      }
  
      public int Id
      {
          get;
          set;
      }
  }
  </code></pre>
  
  <p>I have a MaterielRoulan item which is linked to 0 or 1 Equipement. In turn this equipement might have 1 son (another equipement) or not. The SQL query I previously used is :</p>
  
  <pre><code>select * from MaterielRoulant mr
  left outer join RelationMaterielRoulantEquipement rmre on mr.Id = rmre.MaterielRoulantId
  left outer join Equipement e1 on rmre.EquipementId = e1.Id
  left outer join Filiation f1 on e1.Id = f1.EquipementPereId
  left outer join Equipement e2 on f1.EquipementFilsId = e2.Id
  </code></pre>
  
  <p>So my EF6 translation of this query, which doesn't work, is :</p>
  
  <pre><code>var query = DataContext.Set&lt;MaterielRoulant&gt;()
      .Join(DataContext.Set&lt;RelationMaterielRoulantEquipement&gt;().DefaultIfEmpty(), x =&gt; x.Id, x =&gt; x.MaterielRoulantId, (mr, rmre) =&gt; new { mr, rmre })
      .Join(DataContext.Set&lt;Equipement&gt;().DefaultIfEmpty(), x =&gt; x.rmre.EquipementId, x =&gt; x.Id, (x, e1) =&gt; new { x.mr, x.rmre, e1 })
      .Join(DataContext.Set&lt;Filiation&gt;().DefaultIfEmpty(), x =&gt; x.e1.Id, e =&gt; e.EquipementPereId, (x, f1) =&gt; new { x.mr, x.rmre, x.e1, f1 })
      .Join(DataContext.Set&lt;Equipement&gt;().DefaultIfEmpty(), x =&gt; x.f1.EquipementFilsId, x =&gt; x.Id, (x, e2) =&gt; new { x.mr, x.rmre, e2 })
  </code></pre>
  
  <p>When I start SQL Server Profiler, I see that my query is translated like this :</p>
  
  <pre><code>SELECT 
      [GroupBy1].[A1] AS [C1]
      FROM ( SELECT 
          COUNT(1) AS [A1]
          FROM     [dbo].[MaterielRoulant] AS [Extent1]
          INNER JOIN  (SELECT [Extent2].[EquipementId] AS [EquipementId], [Extent2].[MaterielRoulantId] AS [MaterielRoulantId]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
              LEFT OUTER JOIN [dbo].[RelationMaterielRoulantEquipement] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[Id] = [Join1].[MaterielRoulantId]
          INNER JOIN  (SELECT [Extent3].[Id] AS [Id]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable2]
              LEFT OUTER JOIN [dbo].[Equipement] AS [Extent3] ON 1 = 1 ) AS [Join3] ON ([Join1].[EquipementId] = [Join3].[Id]) OR (([Join1].[EquipementId] IS NULL) AND ([Join3].[Id] IS NULL))
          INNER JOIN  (SELECT [Extent4].[EquipementFilsId] AS [EquipementFilsId], [Extent4].[EquipementPereId] AS [EquipementPereId]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable3]
              LEFT OUTER JOIN [dbo].[Filiation] AS [Extent4] ON 1 = 1 ) AS [Join5] ON ([Join3].[Id] = [Join5].[EquipementPereId]) OR (([Join3].[Id] IS NULL) AND ([Join5].[EquipementPereId] IS NULL))
          INNER JOIN  (SELECT [Extent5].[Id] AS [Id]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable4]
              LEFT OUTER JOIN [dbo].[Equipement] AS [Extent5] ON 1 = 1 ) AS [Join7] ON ([Join5].[EquipementFilsId] = [Join7].[Id]) OR (([Join5].[EquipementFilsId] IS NULL) AND ([Join7].[Id] IS NULL))
      )  AS [GroupBy1]
  </code></pre>
  
  <p>Where it should be :</p>
  
  <pre><code>SELECT 
      [GroupBy1].[A1] AS [C1]
      FROM ( SELECT 
          COUNT(1) AS [A1]
          FROM     [dbo].[MaterielRoulant] AS [Extent1]
          LEFT OUTER JOIN  (SELECT [Extent2].[EquipementId] AS [EquipementId], [Extent2].[MaterielRoulantId] AS [MaterielRoulantId]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
              LEFT OUTER JOIN [dbo].[RelationMaterielRoulantEquipement] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[Id] = [Join1].[MaterielRoulantId]
          LEFT OUTER JOIN  (SELECT [Extent3].[Id] AS [Id]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable2]
              LEFT OUTER JOIN [dbo].[Equipement] AS [Extent3] ON 1 = 1 ) AS [Join3] ON ([Join1].[EquipementId] = [Join3].[Id]) OR (([Join1].[EquipementId] IS NULL) AND ([Join3].[Id] IS NULL))
          LEFT OUTER JOIN  (SELECT [Extent4].[EquipementFilsId] AS [EquipementFilsId], [Extent4].[EquipementPereId] AS [EquipementPereId]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable3]
              LEFT OUTER JOIN [dbo].[Filiation] AS [Extent4] ON 1 = 1 ) AS [Join5] ON ([Join3].[Id] = [Join5].[EquipementPereId]) OR (([Join3].[Id] IS NULL) AND ([Join5].[EquipementPereId] IS NULL))
          LEFT OUTER JOIN  (SELECT [Extent5].[Id] AS [Id]
              FROM   ( SELECT 1 AS X ) AS [SingleRowTable4]
              LEFT OUTER JOIN [dbo].[Equipement] AS [Extent5] ON 1 = 1 ) AS [Join7] ON ([Join5].[EquipementFilsId] = [Join7].[Id]) OR (([Join5].[EquipementFilsId] IS NULL) AND ([Join7].[Id] IS NULL))
      )  AS [GroupBy1]
  </code></pre>
  
  <p>Inner join replaced by outer join.</p>
  
  <p>I have tried using GroupJoin, which still give me the wrong answer with a much more complicated query. I would like to keep the lambda writing as it's used everywhere in my application but at this point any working solution would be great.</p>
  
  <p>Thanks in advance</p>