VB LINQ outer join question

[guyinkalamazoo3]

I have a need where I need to return an extension number for a person even if there is not one listed (NULL). How in LINQ would I do like an outer, or left, join? I was trying to do like a COALESCE using IF, but I get error messages about it not being able to be translated into SQL. Thanks

Dim CurrentEmployeeList As IQueryable(Of Employee)
CurrentEmployeeList = From emp In empdb.CurrentLawsonADFiles
Order By emp.LastName, emp.FirstName Ascending
Join mgr In empdb.CurrentLawsonADFiles On emp.ManagerID Equals mgr.EmpID
Join ext In empdb.EmployeeExtensions On ext.EmployeeNumber Equals emp.EmpID
Select New Employee With {
.FName = emp.FirstName,
.MI = emp.MI,
.LName = emp.LastName,
.FullName = emp.LastName & ", " & emp.FirstName,
.Job = emp.JobDescr,
.Dept = emp.DeptName,
.Empid = emp.EmpID,
.Manager = mgr.LastName & ", " & mgr.FirstName,
.Email = emp.Email,
.Extension = If(Not IsDBNull(ext.ExtensionNumber), ext.ExtensionNumber, 0)
}
dgvAll.DataSource = New BindingSource(CurrentEmployeeList, Nothing)

This is a SQL equivalent of what I am trying to achieve

select c1.FirstName,
c1.LAstName,
c1.JobDescr,
c1.DeptName,
c2.LastName + ', ' + c2.FirstName as mgr,
c1.Email
,ee.ExtensionNumber
from CurrentLawsonADFile c1
join currentlawsonadfile c2 on c1.ManagerID = c2.EmpID
left outer join EmployeeExtensions ee on ee.EmployeeNumber = c1.EmpID
order by lastname, FirstName asc




Brad Allison

Continue reading...