Linq to SQL with multiple Unions

Question

I have a linq union statement that has been giving me some trouble and I can't see where the issue is. Any help would be appreciated.

The error is.... All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I know the error means that I am selecting different amounts of elements in one of the linq statements, but I've examined this query extensively and I haven't been able to see that as the issue.

(From m In db.mainIncidents _
                                    Join r In db.rcas On r.reliabilityID Equals m.reliabilityID _
                                    Join team In db.rcaInvestigationTeams On team.rcaID Equals r.rcaID _
                                    Join user In db.sysUsers On team.teamMemberID Equals user.sysUserID _
                                    Where m.reliabilityID = reliabilityID _
                                    And team.deleted = False _
                                    Select name = user.firstName & " " & user.lastName & " (" & user.id.ToUpper & ")", _
                                    email = user.id & "@test.com", _
                                    user.phone, _
                                    isSponsor = "No", _
                                    isFacilitator = "No", _
                                    isAssetTeamLead = "No").Union _
                               (From m In db.mainIncidents _
                                    Join r In db.rcas On r.reliabilityID Equals m.reliabilityID _
                                    Join at In db.sysUsers On r.assetTeamLeadID Equals at.sysUserID _
                                    Where m.reliabilityID = reliabilityID _
                                    Select name = at.firstName & " " & at.lastName & " (" & at.id.ToUpper & ")", _
                                    email = at.id & "@test.com", _
                                    at.phone, _
                                    isSponsor = "No", _
                                    isFacilitator = "No", _
                                    isAssetTeamLead = "Yes").Union _
                              (From m In db.mainIncidents _
                                    Join r In db.rcas On r.reliabilityID Equals m.reliabilityID _
                                    Join f In db.sysUsers On r.facilitatorID Equals f.sysUserID _
                                    Where m.reliabilityID = reliabilityID _
                                    Select name = f.firstName & " " & f.lastName & " (" & f.id.ToUpper & ")", _
                                    email = f.id & "@test.com", _
                                    f.phone, _
                                    isSponsor = "No", _
                                    isFacilitator = "Yes", _
                                    isAssetTeamLead = "No").Union _
                              (From m In db.mainIncidents _
                                    Join r In db.rcas On r.reliabilityID Equals m.reliabilityID _
                                    Join s In db.sysUsers On r.sponsorID Equals s.sysUserID _
                                    Where m.reliabilityID = reliabilityID _
                                    Select name = s.firstName & " " & s.lastName & " (" & s.id.ToUpper & ")", _
                                    email = s.id & "@test.com", _
                                    s.phone, _
                                    isSponsor = "No", _
                                    isFacilitator = "No", _
                                    isAssetTeamLead = "No")

Answer 1

I can't find the issue in the query. I've used LINQPad and run your query against an adhoc object model and it runs without complaint.

The only thing I can suggest is to remove the repetition. Hopefully then the error will no longer be there.

Here's my factoring.

I do a single db.sysUsers query:

Dim users =
    From u In db.sysUsers
    Select New With { _
        .userId = u.id, _
        .name = u.firstName & " " & u.lastName & " (" & u.id.ToUpper & ")", _
        .email = u.id & "@test.com", _
        .phone = u.phone }

I do a single query on db.mainIncidents & db.rcas:

Dim rcas =
    From m In db.mainIncidents _
    Where m.reliabilityID = reliabilityID _
    Join r In db.rcas On r.reliabilityID Equals m.reliabilityID _
    Select r

And here's the best part, a Role query:

Dim roles =
    From r In rcas _
    From role in ( _
    { _
        New With { .userId = r.assetTeamLeadID, .role = "AssetTeamLead" }, _
        New With { .userId = r.facilitatorID, .role = "Facilitator" }, _
        New With { .userId = r.sponsorID, .role = "Sponsor" } _
    }).Concat(From team In db.rcaInvestigationTeams _
        Where team.deleted = False _
        Where team.rcaID = r.rcaID _
        Select New With { .userId = team.teamMemberID, .role = "TeamMember" }) _
    Select role

And now, the final query:

Dim query =
    From u In users _
    Join r In roles On u.userId Equals r.userId _
    Select u.name, u.email, u.phone, _
    isSponsor = If(r.role = "Sponsor", "Yes", "No"), _
    isFacilitator = If(r.role = "Facilitator", "Yes", "No"), _
    isAssetTeamLead = If(r.role = "AssetTeamLead", "Yes", "No")

I hope this helps.