Outer right join on Linq c#

Question

How can I perform a right join ? I'm trying to use the DefaultIfEmpty() function, but without effect.

There is my code :

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

The sql query generated :

{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}

The sql query expected:

{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}

Hint: A right outer join is a [left outer join](https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins) with the left set on the right and vice versa. — spender, Jun 23 '17 at 10:31
Forget about joins in EF - use navigation properties. You already did that in one place - `abs.DepartementProject`, which generates the `INNER JOIN` you see in the SQL query. Since you are not using the `c` variable in your LINQ query, the explicit join (left outer btw) is simply ignored by EF query translator. — Ivan Stoev, Jun 23 '17 at 10:39
What happens if you change `from c in g.DefaultIfEmpty()` to `from abs in g.DefaultIfEmpty()` ? See https://stackoverflow.com/a/3413732/34092 . — mjwills, Jun 23 '17 at 10:47

score 0 · Answer 1 · answered Jun 23 '17 at 19:20

The join doesn't seem to serve any purpose in your LINQ code, so I simplified and got rid of some peculiar usage. I used let to be DRY, but it may not be more efficient for IQueryable, I am not sure.

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

thanks for your proposition, but, I still have the same problem, my expectation it's to show all department even when the count is null, — Le-Mr-Ruyk, Jun 28 '17 at 14:50

score 0 · Accepted Answer · answered Jun 28 '17 at 15:15

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

Outer right join on Linq c#

2 Answers2