在linq中编译的多个左联接

Question

在我的API项目中，使用EntityFramework，我必须转换以下SQL语句

select p.cod_paquete, p.nombre, a.descripcion, a.proceso, a.color, a.descripcion, (select count(*) from paqueteAcabado where paquete=a.paquete and color=a.color), coalesce(pc.titulo,''), case when pc.opcional=1 then 1 else 0 end as opcional, pa.optativo, a.opcional 
from paqueteAPP pa 
inner join paquete p on p.cod_paquete=pa.paquete 
left join paqueteAcabado a on a.paquete=p.cod_paquete and a.opcional=1 
left join coste c on c.cod_coste=a.acabado 
left join paqueteColor pc on pc.paquete=p.cod_paquete and pc.color=a.color 
where pa.agrupacionProductoProducto= '298'
order by coalesce(pa.orden,0), p.cod_paquete, a.color, a.descripcion 

类别

public class SQLResultadoPaquetesAcabado
{
public short cod_paquete { get; set; }
public string nombre { get; set; }
public string descripcion { get; set; }
public short proceso { get; set; }
public string color { get; set; }
public int? paquetesColor { get; set; }
public string titulo { get; set; }
public bool? PaqueteColoropcional { get; set; }
public bool optativo { get; set; }
public bool? acabadoOpcional { get; set; }
}

Linq中翻译的sql

int codigo = 298;
Paquetes = (from paqueteAPP pa in db.paqueteAPP
join paquete p in db.paquete on pa.paquete equals p.cod_paquete
join paqueteAcabado a in db.paqueteAcabado on p.cod_paquete equals a.paquete into pcacabado
from pacabado in pcacabado.DefaultIfEmpty()
join coste c in db.coste on pacabado.acabado equals c.cod_coste into pacabadocoste1
from pcosteacabado in pacabadocoste1.DefaultIfEmpty()
join paqueteColor pc in db.paqueteColor on p.cod_paquete equals pc.paquete into pcolor
from pacolor in pcolor.DefaultIfEmpty()
where pa.agrupacionProductoProducto == codigo && pacabado.opcional == true
select new SQLResultadoPaquetesAcabado
{
cod_paquete = p.cod_paquete,
nombre = p.nombre,
descripcion = pacabado.descripcion,
proceso = pacabado.proceso,
color = pacabado.color,
paquetesColor = (from paqueteAcabado pa in db.paqueteAcabado
where pa.paquete == pacabado.paquete && pa.color == 
pacabado.color
select pa).Count(),
titulo = pacolor.titulo,
PaqueteColoropcional = pacolor.opcional,
optativo = pa.optativo,
acabadoOpcional = pacabado.opcional
});

从Linq转换为SQL时获得的SQL

SELECT COUNT
[Project1].[agrupacionProductoProducto] AS [agrupacionProductoProducto], 
[Project1].[cod_paquete] AS [cod_paquete], 
[Project1].[nombre] AS [nombre], 
[Project1].[descripcion] AS [descripcion], 
[Project1].[proceso] AS [proceso], 
[Project1].[color] AS [color], 
[Project1].[C1] AS [C1], 
[Project1].[titulo] AS [titulo], 
[Project1].[opcional1] AS [opcional], 
[Project1].[optativo] AS [optativo], 
[Project1].[opcional] AS [opcional1]
FROM ( SELECT 
[Filter1].[agrupacionProductoProducto] AS [agrupacionProductoProducto], 
[Filter1].[optativo] AS [optativo], 
[Filter1].[cod_paquete] AS [cod_paquete], 
[Filter1].[nombre] AS [nombre], 
[Filter1].[proceso] AS [proceso], 
[Filter1].[descripcion] AS [descripcion], 
[Filter1].[opcional] AS [opcional], 
[Filter1].[color] AS [color], 
[Extent4].[titulo] AS [titulo], 
[Extent4].[opcional] AS [opcional1], 
(SELECT 
COUNT(1) AS [A1]
FROM [dbo].[paqueteAcabado] AS [Extent5]
WHERE ([Extent5].[paquete] = [Filter1].[paquete1]) AND (([Extent5].[color] = [Filter1].[color]) OR (([Extent5].[color] IS NULL) AND ([Filter1].[color] IS NULL)))) AS [C1]
FROM   (SELECT [Extent1].[agrupacionProductoProducto] AS [agrupacionProductoProducto], [Extent1].[optativo] AS [optativo], [Extent2].[cod_paquete] AS [cod_paquete], [Extent2].[nombre] AS [nombre], [Extent3].[proceso] AS [proceso], [Extent3].[paquete] AS [paquete1], [Extent3].[descripcion] AS [descripcion], [Extent3].[opcional] AS [opcional], [Extent3].[color] AS [color]
FROM   [dbo].[paqueteAPP] AS [Extent1]
INNER JOIN [dbo].[paquete] AS [Extent2] ON [Extent1].[paquete] = [Extent2].[cod_paquete]
INNER JOIN [dbo].[paqueteAcabado] AS [Extent3] ON [Extent2].[cod_paquete] = [Extent3].[paquete]
WHERE 1 = [Extent3].[opcional] ) AS [Filter1]
LEFT OUTER JOIN [dbo].[paqueteColor] AS [Extent4] ON [Filter1].[cod_paquete] = [Extent4].[paquete]
WHERE [Filter1].[agrupacionProductoProducto] = '298'
)  AS [Project1]

我的问题是SQL由三个左联接组成

使用linq，我只能生成一个左联接，正如您在生成linq到sql的结果中看到的那样

SQL

left join paqueteAcabado a on a.paquete=p.cod_paquete and a.opcional=1 
left join coste c on c.cod_coste=a.acabado 
left join paqueteColor pc on pc.paquete=p.cod_paquete and pc.color=a.color 

Linq

INNER JOIN [dbo].[paquete] AS [Extent2] ON [Extent1].[paquete] = [Extent2].[cod_paquete]
INNER JOIN [dbo].[paqueteAcabado] AS [Extent3] ON [Extent2].[cod_paquete] = [Extent3].[paquete]
WHERE 1 = [Extent3].[opcional] ) AS [Filter1]
LEFT OUTER JOIN [dbo].[paqueteColor] AS [Extent4] ON [Filter1].[cod_paquete] = [Extent4].[paquete]

我的问题是，如何在不使用distinct的情况下通过linq成功地复制SQL？因为使用distinct，我得到了我想要的结果，但我得到了120个重复的行，我不想要

Answer 1

我自己编写代码，但代码转换非常困难。然而，我试图解释如何创建像sql查询这样的左外部联接。

请记住，这不是最终查询。您必须添加内部查询(我认为您可以(，若您得到的联接关键字不匹配异常，您应该添加相同的名称。

现在代码在这里，我尝试转换一些部分：

var joinTest = context.paqueteAPP.Join(context.paquete,
pa => pa.paquete,
p => p.cod_paquete,
(pa, p) =>
new {p.cod_paquete, p.nombre,p.cod_paquete})
.GroupJoin(context.paqueteAcabado,
a => new
{
a.paquete,
a.opcional
}, 
temp => new {paquete=temp.cod_paquete,opcional=1},
(temp, paqueteAcabado) => new {temp.cod_paquete, temp.nombre, paqueteAcabado})
.SelectMany(s => s.paqueteAcabado.DefaultIfEmpty(),
(s, paqueteAcabado) => new {s.cod_paquete, s.nombre,paqueteAcabado.descripcion, paqueteAcabado.proceso, paqueteAcabado.color, paqueteAcabado.descripcion,paqueteAcabado.acabado})
.GroupJoin(context.coste,
a => a.acabado, c => c.cod_coste,
(temp, coste) => new { temp.cod_paquete, temp.nombre,temp.descripcion, temp.proceso, temp.color, temp.descripcion, coste })
.SelectMany(s => s.coste.DefaultIfEmpty(),
(s, coste) => new { s.cod_paquete, s.nombre,s.descripcion, s.proceso, s.color, s.descripcion, coste.Name })
.ToList();

此查询创建1个内部联接和2个左侧联接。

如果您需要更多帮助，请添加实体(您需要的(和一些数据。