CODEWITHSUNDEEP
c#linqjoin
Programming Dreamer
Programming Dreamer

Reputation: 235

LINQ: Inner join 2 table + outer join 1 table

I have 2 inner joins (3 tables) but I don't know and I find it hard to implement my research about outer join in LINQ. How do I change the last inner join to outer join, such that column will still join even if the column (Role) is null?

Here's an existing SQL version of this which I want to convert to LINQ:

SELECT dbo.EmployeeAccess.id, dbo.EmployeeAccess.EmpNo, dbo.EmployeeAccess.RoleID, dbo.EmployeeAccess.Active, dbo.EmployeeAccessLevel.Role, 
                  dbo.View_HCM.LNameByFName

FROM  dbo.EmployeeAccess LEFT OUTER JOIN
      dbo.EmployeeAccessLevel ON dbo.EmployeeAccess.RoleID = dbo.EmployeeAccessLevel.id INNER JOIN
      dbo.View_HCM ON dbo.EmployeeAccess.EmpNo = dbo.View_HCM.EmpNo

LINQ I now have with 2 inner joins:

    (from ea in context.EmployeeAccesses
                    join vh in context.View_HCM on (Int16)ea.EmpNo equals vh.EmpNo
                    join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id 
                    select new EmployeeWithEmail{

                        EmpNum = ea.EmpNo ?? 0,
                        EmailAddress = vh.EmailAddress,                          
                        LNameByFname = vh.LNameByFName,                                  
                        Active2 = ea.Active ?? false

                    }).ToList();
        }

Upvotes: 2

Views: 629

Answers (1)

crthompson
crthompson

Reputation: 15865

Linq's outer join syntax uses 2 parts. First an into then DefaultIfEmpty

In your case, an outer join might look like this:

(from ea in context.EmployeeAccesses
join vh in context.View_HCM on (Int16)ea.EmpNo equals vh.EmpNo
join rl in context.EmployeeAccessLevels on ea.RoleID equals rl.id into outer_join
from subjoin in outer_join.DefaultIfEmpty()
select new EmployeeWithEmail{

    EmpNum = ea.EmpNo ?? 0,
    EmailAddress = vh.EmailAddress,                          
    LNameByFname = vh.LNameByFName,                                  
    Active2 = ea.Active ?? false

}).ToList();

There are many tutorials on how to create the outer join in LINQ.

Upvotes: 2

Related Questions