Left join query with EF 6

Question

Left join query with EF 6

c# entity-framework-6 sql-server

Question

First, here is the simplified EF model I'm using :

public partial class MaterielRoulant : IKeyed<int>
{
    public MaterielRoulant()
    {
        this.RelationsMaterielRoulantEquipement = new List<RelationMaterielRoulantEquipement>();
    }

    public int Id
    {
        get;
        set;
    }

    public virtual ICollection<RelationMaterielRoulantEquipement> RelationsMaterielRoulantEquipement
    {
        get;
        private set;
    }
}

public partial class RelationMaterielRoulantEquipement : IKeyed<int>
{
    public int EquipementId
    {
        get;
        set;
    }

    public int Id
    {
        get;
        set;
    }

    public int MaterielRoulantId
    {
        get;
        set;
    }

}

public partial class Equipement : IKeyed<int>, IEditable, IArchivable, IAuditable
{
    public int Id
    {
        get;
        set;
    }
}

public partial class Filiation : IKeyed<int>
{
    public int EquipementFilsId
    {
        get;
        set;
    }

    public int EquipementPereId
    {
        get;
        set;
    }

    public int Id
    {
        get;
        set;
    }
}

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 :

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

So my EF6 translation of this query, which doesn't work, is :

var query = DataContext.Set<MaterielRoulant>()
    .Join(DataContext.Set<RelationMaterielRoulantEquipement>().DefaultIfEmpty(), x => x.Id, x => x.MaterielRoulantId, (mr, rmre) => new { mr, rmre })
    .Join(DataContext.Set<Equipement>().DefaultIfEmpty(), x => x.rmre.EquipementId, x => x.Id, (x, e1) => new { x.mr, x.rmre, e1 })
    .Join(DataContext.Set<Filiation>().DefaultIfEmpty(), x => x.e1.Id, e => e.EquipementPereId, (x, f1) => new { x.mr, x.rmre, x.e1, f1 })
    .Join(DataContext.Set<Equipement>().DefaultIfEmpty(), x => x.f1.EquipementFilsId, x => x.Id, (x, e2) => new { x.mr, x.rmre, e2 })

When I start SQL Server Profiler, I see that my query is translated like this :

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]

Where it should be :

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]

Inner join replaced by outer join.

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.

Thanks in advance

Pierre-Antoine

View more on Stack Overflow

Magnus · Accepted Answer · 8/11/2015 10:48:23 AM

You can write it like this:

var q = 
    from mr in db.MaterielRoulant
    from rmre in db.RelationMaterielRoulantEquipement.Where(x => mr.Id == x.MaterielRoulantId).DefaultIfEmpty()
    from e1 in db.Equipement.Where(x => rmre.EquipementId == x.Id).DefaultIfEmpty()
    from f1 in db.Filiation.Where(x => e1.Id == x.EquipementPereId).DefaultIfEmpty()
    from e2 in db.Equipement.Where(x => f1.EquipementFilsId == x.Id).DefaultIfEmpty()
    select new {...}

For expression syntax it'll look like this (for the first 2 joins)

db.MaterielRoulant
   .SelectMany(
      mr => 
         db.RelationMaterielRoulantEquipement
            .Where (x => mr.Id == x.MaterielRoulantId)
            .DefaultIfEmpty(), 
      (mr, rmre) => 
         new  
         {
            mr = mr, 
            rmre = rmre
         }
   )
   .SelectMany(
      temp => 
         db.Equipement
            .Where (x => rmre.EquipementId == x.Id)
            .DefaultIfEmpty(), 
      (temp, e1) =>  
        new  
        {
          mr = temp.mr, 
          rmre = temp.rmre, 
          e1 = e1
       }
   )...

Left join query with EF 6

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related