左连接查询EF 6
本文关键字:EF 查询 连接 | 更新日期: 2023-09-27 18:07:20
首先,这是我使用的简化EF模型:
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;
}
}
我有一个与0或1个装备相连的materielroran物品。反过来,这个设备可能有一个儿子(另一个设备)或没有。我之前使用的SQL查询是:
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
所以我的这个查询的EF6翻译,这不起作用,是:
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 })
当我启动SQL Server Profiler时,我看到我的查询是这样翻译的:
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]
实际位置:
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]
内连接被外连接取代。
我试过使用GroupJoin,它仍然给我一个更复杂的查询错误的答案。我想保持lambda写作,因为它在我的应用程序中无处不在,但在这一点上任何工作的解决方案将是伟大的。
Thanks in advance
你可以这样写:
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 {...}
对于表达式语法,它看起来像这样(对于前两个连接)
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
}
)...