Left Join in VB.NET LINQ

Question

I'm trying to figure out how to perform a LEFT JOIN in Linq but struggling...

I have an existing query

Dim people As List(Of SimplePerson)

Using gpd As New peopleDatabase
    people = (From person In gpd.People Join office In gpd.Offices On person.office_ref Equals office.officeID
              Join personphone In gpd.x_PersonPhone On person.personId Equals personphone.person_ref
              Join telephoneNumber In gpd.TelephoneNumbers On personphone.telephoneNumber_ref Equals telephoneNumber.telephoneNumberId
              Where (person.preferredGivenName & " " & person.familyName).StartsWith(searchTerm) And telephoneNumber.x_TelephoneNumberTelephoneNumberType.FirstOrDefault.b_telephoneNumberType.telephoneNumberTypeId = 1 And person.active = True
              Select New SimplePerson() With {
                  .Name = person.preferredGivenName & " " & person.familyName,
                  .EmailAddress = person.emailAddress,
                  .Title = person.jobTitle_l,
                  .ResId = person.sourceKey,
                  .OrganisationId = person.organisation_ref.Value,
                  .OfficeName = office.name_l,
                  .TelephoneNumber = telephoneNumber.formattedNumberInternational
              }
          ).OrderBy(Function(n) n.Name).Take(20).ToList

End Using

Return people

For context, this is a query I'm using for a jQueryUI Autocomplete widget. SimplePerson is a POCO I'm using which just contains the set of properties listed in the code above.

What I'm trying to achieve is to change the query so that it lists people regardless of whether they have any phone number i.e. they have no entries in the table x_PersonPhone. Based on this answer, I believe DefaultIfEmpty comes into it somewhere, but I can't see how I apply this to x_PersonPhone and then continue to join on that table for the matching rows.

score 0 · Answer 1 · answered Feb 24 '20 at 15:15

You should follow the linked answer and use Group Join. However, just as you would continue to LEFT OUTER JOIN in SQL following a left joined dependent table, you need to left join both x_PersonPhone and then TelephoneNumbers. And since you will join twice, you can't just use only one Group keyword; you must use two. See the Group By Clause syntax here, specifically the Into <alias> = Group construct.

Dim people As List(Of SimplePerson)

Using gpd As New peopleDatabase()

    people =
        (From person In gpd.People
            Join office In gpd.Offices On person.office_ref Equals office.OfficeID
            Group Join personphone In gpd.x_PersonPhone On person.PersonID Equals personphone.person_ref
                Into g1 = Group From personphone In g1.DefaultIfEmpty()
            Group Join telephoneNumber In gpd.TelephoneNumbers On personphone.telephoneNumber_ref Equals telephoneNumber.telephoneNumberId
                Into g2 = Group From telephoneNumber In g2.DefaultIfEmpty()
            Where (person.preferredGivenName & " " & person.familyName).StartsWith(searchTerm) _
                And telephoneNumber.x_TelephoneNumberTelephoneNumberType.FirstOrDefault().b_telephoneNumberType.telephoneNumberTypeId = 1 _
                And person.active = True
            Select New SimplePerson() With {
                .Name = person.preferredGivenName & " " & person.familyName,
                .EmailAddress = person.emailAddress,
                .Title = person.jobTitle_l,
                .ResId = person.sourceKey,
                .OrganisationId = person.organisation_ref.Value,
                .OfficeName = office.name_l,
                .telephoneNumber = If(g2 Is Nothing, "No telephone", telephoneNumber.formattedNumberInternational)}
            ).OrderBy(Function(n) n.Name).Take(20).ToList()

End Using

Return people

I can't really test this. You may need to change the line in Select New SimplePerson() With { ... from

.telephoneNumber = If(g2 Is Nothing, "No telephone", telephoneNumber.formattedNumberInternational)

to

.telephoneNumber = If(telephoneNumber Is Nothing, "No telephone", telephoneNumber.formattedNumberInternational)

If it works, please let me know and I'll update the answer.

Left Join in VB.NET LINQ

1 Answers1