There are two approaches I am considering, but I have to look into the performance before I decide. One approach puts all the conditions in the WHERE clause, and the other approach UNIONs two initial result sets.
Dim combinedCompanies =
From P in context.Person
From C in context.Company
Where
C.CompanyName Equals P.CompanyName
And P.CompanyName <> "Unemployed"
Or
(
P.CompanyName = "Unemployed" And
C.VolunteerJob = True
)
This is the version that my coworker recommended:
Dim matchingCompanies =
From P In context.Person
Join C In context.Company
On C.CompanyName Equals P.CompanyName
Where
P.CompanyName <> "Unemployed"
Dim volunteerCompanies =
From P In context.Person
From C In context.Company
Where
P.CompanyName = "Unemployed" And
C.VolunteerJob = True
Dim combinedCompanies = matchingCompanies.Union(volunteerCompanies)
I like the brevity of the first approach, but I'm not sure it will be as performant as the second approach.