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
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
}
)...