Linq c外部加入#
问题描述:
如何执行正确的加入?我试图使用DefaultIfEmpty()
函数,但没有效果。Linq c外部加入#
有我的代码:
var AbsByDepartmentADM = (from abs in _dbContext.Absences
join e in _dbContext.DepartementProjects on abs.DepartementProjectID equals e.Id
into g
from c in g.DefaultIfEmpty()
where abs.Profil == "SHT"
group abs by new { abs.DepartementProject.Label } into z
select new
{
Matricule = z.Key.Label,
NumberOfAbsence = z.Count(),
WorkedHours = z.Sum(a => a.WorkedHours),
AbsencesHours = (8* z.Count() - z.Sum(a => a.WorkedHours))
} into H
orderby H.NumberOfAbsence descending
select H);
SQL查询生成:
{SELECT
[Project1].[C3] AS [C1],
[Project1].[Label] AS [Label],
[Project1].[C1] AS [C2],
[Project1].[C2] AS [C3],
[Project1].[C4] AS [C4]
FROM (SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[A2] AS [C2],
1 AS [C3],
CAST(8 * [GroupBy1].[A3] AS float) - [GroupBy1].[A4] AS [C4],
[GroupBy1].[K1] AS [Label]
FROM (SELECT
[Extent2].[Label] AS [K1],
COUNT(1) AS [A1],
SUM([Extent1].[WorkedHours]) AS [A2],
COUNT(1) AS [A3],
SUM([Extent1].[WorkedHours]) AS [A4]
FROM [dbo].[Absences] AS [Extent1]
INNER JOIN [dbo].[DepartementProjects] AS [Extent2] ON [Extent1].[DepartementProjectID] = [Extent2].[Id]
WHERE N'SHT' = [Extent1].[Profil]
GROUP BY [Extent2].[Label]
) AS [GroupBy1]
) AS [Project1] ORDER BY [Project1].[C1] DESC}
SQL查询预计:
{SELECT
[Project1].[C3] AS [C1],
[Project1].[Label] AS [Label],
[Project1].[C1] AS [C2],
[Project1].[C2] AS [C3],
[Project1].[C4] AS [C4]
FROM (SELECT
[GroupBy1].[A1] AS [C1],
[GroupBy1].[A2] AS [C2],
1 AS [C3],
CAST(8 * [GroupBy1].[A3] AS float) - [GroupBy1].[A4] AS [C4],
[GroupBy1].[K1] AS [Label]
FROM (SELECT
[Extent2].[Label] AS [K1],
COUNT(1) AS [A1],
SUM([Extent1].[WorkedHours]) AS [A2],
COUNT(1) AS [A3],
SUM([Extent1].[WorkedHours]) AS [A4]
FROM [dbo].[Absences] AS [Extent1]
RIGHT JOIN [dbo].[DepartementProjects] AS [Extent2] ON [Extent1].[DepartementProjectID] = [Extent2].[Id]
WHERE N'SHT' = [Extent1].[Profil]
GROUP BY [Extent2].[Label]
) AS [GroupBy1]
) AS [Project1] ORDER BY [Project1].[C1] DESC}
答
var AbsByDepartmentADM = from department in _dbContext.DepartementProjects
join abs in _dbContext.Absences on department.Id equals abs.DepartementProjectID into groupedResult
from groupedResultRight in groupedResult.DefaultIfEmpty()
group groupedResultRight by department.Label into grouped
let NumberOfAbsence = grouped.Count(t => t.DepartementProjectID != null)
let WorkedHours = grouped.Sum(a => a.WorkedHours != null ? a.WorkedHours : 0)
select new
{
DepartmentId = grouped.Key,
NumberOfAbsence,
WorkedHours,
AbsencesHours = (8 * NumberOfAbsence - WorkedHours)
};
答
的加入似乎并不起任何作用在你的LINQ代码中,所以我简化了一些特殊的用法。我用let
做了DRY,但对于IQueryable
可能效率不高,我不确定。
var AbsByDepartmentADM2 = from abs in _dbContext.Absences
where abs.Profil == "SHT"
group abs by abs.DepartementProject.Label into absg
let NumberOfAbsence = absg.Count()
let WorkedHours = absg.Sum(abs => abs.WorkedHours)
orderby NumberOfAbsence descending
select new {
Matricule = absg.Key,
NumberOfAbsence,
WorkedHours,
AbsencesHours = (8 * NumberOfAbsence - WorkedHours)
};
+0
感谢您的建议,但是,我仍然有同样的问题,我的期望是显示所有部门,即使计数为空时, –
提示:右外部联接是[左外连接(https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins ),左侧设置在右侧,反之亦然。 – spender
忘记EF中的连接 - 使用导航属性。你已经在一个地方做过 - 'abs.DepartementProject',它生成你在SQL查询中看到的INNER JOIN。由于在LINQ查询中没有使用'c'变量,所以显式连接(left outer btw)简单地被EF查询转换器忽略。 –
如果将g.DefaultIfEmpty()中的c从'g.DefaultIfEmpty()'中的abs改为'abs?会发生什么?请参阅https://*.com/a/3413732/34092。 – mjwills