none
VB LINQ outer join question RRS feed

  • Question

  • 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



    Friday, December 13, 2019 2:00 PM

Answers

  • http://www.obelink.com/index.php/2016/01/04/left-outer-join-in-linq-with-visual-basic-net/

    Bing and Google are your friends.

    Friday, December 13, 2019 3:31 PM

All replies

  • Perhaps making the field nullable, same in the model then you can check via .HasValue via IF. Another option is to do the check via a let clause.

    Then there is Convert.IsDBNull.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, December 13, 2019 3:03 PM
    Moderator
  • http://www.obelink.com/index.php/2016/01/04/left-outer-join-in-linq-with-visual-basic-net/

    Bing and Google are your friends.

    Friday, December 13, 2019 3:31 PM
  • Funny too that I JUST found this article and yes, it worked.

    Here is my new code

     CurrentEmployeeList = From emp In empdb.CurrentLawsonADFiles
                                      From ext In empdb.EmployeeExtensions.Where(Function(ext) ext.EmployeeNumber = emp.EmpID).DefaultIfEmpty()
                                      Order By emp.LastName, emp.FirstName Ascending
                                      Join mgr In empdb.CurrentLawsonADFiles On emp.ManagerID Equals mgr.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 = ext.ExtensionNumber}
                dgvAll.DataSource = New BindingSource(CurrentEmployeeList, Nothing)


    Brad Allison

    Friday, December 13, 2019 3:58 PM
  • I know I already closed this question, but now I found I have some missing data and it is due to this line here:  Join mgr In empdb.CurrentLawsonADFiles On emp.ManagerID Equals mgr.EmpID

    If there is not a managerID, then it is not selecting the row.  I solved the other issue and have tried a few things, but how would I join a table on itself where employeeid = managerid even if managerID is null?


    Brad Allison

    Monday, December 30, 2019 6:44 PM