SQL Server : implement "IN(...)" in Linq join condition

Question

I am trying to convert an old T-SQL query to LINQ. The old query is similar to:

Person 
(
    personId bigint,
    username varchar(18)
)

Phones 
(
    phoneId bigint,
    personId bigint,         [FK to Persons.personId]
    phoneNumber varchar(30),
    phoneType varchar(1)     [could be any of" "O", "H", 'Y" or a few others]
)

SELECT 
    person.personId,
    person.username,
    phone.phoneNumber
FROM
    Persons persons
LEFT JOIN 
    Phones phones ON person.personId = phones.personId
                  AND phones.phoneType IN ("O", "Y")
WHERE 
    person.username != ""
    AND person.ActualEndDate IS NOT NULL

How do I implement the AND phones.phoneType in ("O", "Y") clause in linq? I can do one at a time as in:

netids = (
    from person in Persons
    join phone in Phones
        on new {
            person.PersonId,
            phoneTypeAbbv = "Y"
        } equals new {
            phone.PersonId,
            phoneTypeAbbv = phone.PhoneTypeAbbv.ToUpper()
        } into phoneList
    from phoneListItem in phoneList.DefaultIfEmpty()
    where !person.username.Equals("")
          & !person.ActualEndDate.HasValue
    select new NetidToLync {
        PersonId = person.PersonId,
        Netid = person.username.Trim().ToLower(),
        Lync = phoneListItem.PhoneNumber.Trim().ToLower() ?? ""
    }
).ToList();

Is this Linq to Entities (Entity Framework)? – Rui Jarimba Oct 16 '18 at 19:21 — Rui Jarimba, Oct 16 '18 at 19:21

score 2 · Accepted Answer · answered Oct 16 '18 at 19:22

That does not, logically, need to be a condition of the join. In fact, the way your table structure is, the only conditiion for the join is Phones.personId = Person.personId.

So, put that condition in the WHERE clause where it belongs. Something like

netids = (
    from person in Persons
    join phone in Phones
        on person.PersonId == phone.PersonId
    where !person.username.Equals("")
          & person.ActualEndDate.HasValue
          & (phone.phoneType.Equals("Y") | phone.phoneType.Equals("O"))
    select new NetidToLync {
        PersonId = person.PersonId,
        Netid = person.username.Trim().ToLower(),
        Lync = phone.phoneNumber.Trim().ToLower() ?? ""
    }
).ToList();

score 1 · Answer 2 · answered Oct 16 '18 at 19:14

1

You can use the .Any() clause

e.g.

phoneList.Any(type => type == "O" || type == "Y")

answered Oct 16 '18 at 19:14

raterus

1,980
20
23

score 1 · Answer 3 · answered Oct 16 '18 at 19:44

With properly defined relations in the database you shouldn't need joins.

var result = 
    from person in Persons
    where !person.username.Equals("")
          & !person.ActualEndDate.HasValue
    let phones = person.Phones
         .Where( ph => new[] {"O","Y"}.Contains(ph.PhoneType) )
    select new {
        PersonId = person.PersonId,
        Netid = person.username.Trim().ToLower(),
        Lync = phones
    };

SQL Server : implement "IN(...)" in Linq join condition

3 Answers3