Внешнее правое соединение на Linq C#

Question

Как я могу выполнить правильное соединение? Я пытаюсь использовать DefaultIfEmpty() функция, но без эффекта.

Вот мой код:

 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}

Answer 1

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

Answer 2

Объединение, кажется, не служит какой-либо цели в вашем коде LINQ, поэтому я упростил и избавился от некоторого специфического использования. я использовал let быть сухим, но это не может быть более эффективным для 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)
                      };