Group Join child column value when no child column row RRS feed

  • Question

  •    Private Structure structGetServiceServiceRowsOpenWithServiceQueueIDPriorityStructureGroupJoin
          Dim Status As String
          Dim ServiceGroup As String
          Dim AssetGroup As String
          Dim Category As String
          Dim ServiceID As Integer
          Dim Requestor As String
          Dim Assignee As String
          Dim Receive As String
          Dim SLA As Integer
          Dim Target As String
          Dim Description As String
          Dim RequestorLAXUserID As Integer
          Dim Severity As String
          Dim Level As Integer
          Dim Queue As String
          Dim Department As String
          Dim SubDepartment As String
          Dim Approver As String
          Dim AssigneeUserID As String
       End Structure
       Public Shared Function GetServiceServiceRowsOpenWithServiceQueueIDPriorityStructureGroupJoin(ByVal ServiceQueueID As String) As DataTable
          Dim dt As New DataTable
          Using dc As dcServiceDataContext = New dcServiceDataContext(strServiceConnectionString)
             dc.ObjectTrackingEnabled = False
                Dim query = From tblServiceService In dc.tblServiceServices _
                            Group Join tblServiceServiceAssignee In dc.tblServiceServiceAssignees On tblServiceServiceAssignee.ServiceID Equals tblServiceService.ServiceID Into Group
                            From tblServiceServiceAssignee In Group.DefaultIfEmpty() _
                            Where tblServiceService.ServiceQueueID = ServiceQueueID _
                          AndAlso tblServiceService.CloseDate Is Nothing _
                          Order By tblServiceService.ServiceStatusID Descending, _
                                   tblServiceService.ServiceID Descending _
                            Select New structGetServiceServiceRowsOpenWithServiceQueueIDPriorityStructureGroupJoin With _
                              {.Status = tblServiceService.ServiceStatusID, _
                               .ServiceGroup = tblServiceService.ServiceGroupID, _
                               .AssetGroup = tblServiceService.AssetGroupID, _
                               .Category = tblServiceService.ServiceCategoryID, _
                               .ServiceID = tblServiceService.ServiceID, _
                               .Requestor = GetServiceUserLastNameFirstNameWithLAXUserID(tblServiceService.RequestorLAXUserID), _
                               .Assignee = tblServiceServiceAssignee.AssigneeUserID, _
                               .Receive = Format(tblServiceService.ReceiveDate, "yy.MM.dd"), _
                               .SLA = CalculateSLADays(tblServiceService.ServiceID, tblServiceService.TargetDate), _
                               .Target = Format(tblServiceService.TargetDate, "yy.MM.dd"), _
                               .Description = If(tblServiceService.Brief IsNot Nothing, tblServiceService.Brief, tblServiceService.Description), _
                               .RequestorLAXUserID = tblServiceService.RequestorLAXUserID, _
                               .Severity = tblServiceService.ServiceSeverityID, _
                               .Level = tblServiceService.ServiceLevel, _
                               .Queue = tblServiceService.ServiceQueueID, _
                               .Department = NStr(tblServiceService.DepartmentID), _
                               .SubDepartment = NStr(tblServiceService.SubDepartmentID), _
                               .Approver = tblServiceService.ApproverUserID, _
                               .AssigneeUserID = tblServiceServiceAssignee.AssigneeUserID}
                '.Assignee = tblServiceServiceAssignee.AssigneeUserID, _ 'works, returns the correct assignee LogonID string when assignee row present, empty string when no assignee row present
                '.Assignee = If(tblServiceServiceAssignee.AssigneeUserID IsNot Nothing, GetServiceUserLastNameFirstNameWithLAXUserID(tblServiceServiceAssignee.AssigneeLAXUserID), String.Empty), _ 'compiles, but LINQ translation fails in execution
                '.Assignee = GetServiceUserLastNameFirstNameWithUserID(tblServiceServiceAssignee.AssigneeUserID), _ 'return an assignee name on all rows, but assignee name is from another row when no assignee row exists
                dt = query.CopyToDataTable()
                'resort table
                dt.DefaultView.Sort = "SLA asc"
                dt = dt.DefaultView.ToTable()
             Catch exc As Exception
                'Call WinExcept.ExceptionHandler.ExceptionLinq(cService, clinqService, strServiceConnectionString, System.Reflection.MethodInfo.GetCurrentMethod().Name, exc)
                Return dt
             End Try
          End Using
          Return dt
       End Function

    In the above code the output line that starts with: .Assignee = is the one line I need help on.

    See the three commented lines above this line: dt = query.CopyToDataTable()

    I have run each of these three lines, with the result obtained described in the comment that ends the code line.

    What I am attempting to do is to use an integer value (LAXUserID) to query a user table and return the User's name in "LastName, FirstName" format.  In the database, the LAXUserID is in a child table (tblServiceServiceAssignee) that may not be present for all parent rows (in table tblServiceService).  Where a child row does not exist, I want to show string.empty.

    I have coded this in SQL with a LEFT JOIN for tblServiceServiceAssignee, and it works fine.  I just cannot figure out how to make LINQ return the same result.

    Thanks in advance.

    Dean Slindee

    Thursday, May 9, 2013 9:30 PM