Linq to SQL with multiple Unions
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 = "N开发者_StackOverflow中文版o", _
isFacilitator = "No", _
isAssetTeamLead = "No")
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.
精彩评论