CODEWITHSUNDEEP
linqentity-framework-4.1
bdaniel7
bdaniel7

Reputation: 197

LINQ join with multiple conditions?

I have the following working SQL query:

select a.Id, a.Name
from Addresses a join Companies c 
                on c.AddressId = a.Id
                or c.MailAddressId = a.Id
                or c.Id = a.CompanyId
where c.Id = *CompanyId* and a.Name = *AddressName*

that checks if an Address with the provided Address name is linked to a company with the provided Company id.

How can I express this in LINQ for EF?

Update: The Address and Company classes are as follows (only details relevant to this question are included):

public class Address
{
    public int Id {get; set;}
    public string Name {get; set;}
    public int? CompanyId {get; set;}
    public virtual Company {get; set;}
}
public class Company
{
    public int Id {get; set;}
    public string Name {get; set;}

    public int AddressId {get; set;}
    public virtual Address Address {get; set;}

    public int? MailAddressId {get; set;}
    public virtual Address MailAddress {get; set;}

    public virtual ICollection<Address> DeliveryAddresses {get; set;}
}

Thank you.

Upvotes: 3

Views: 1676

Answers (1)

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50712

LINQ supports only equal joins. in other cases you should use cross-join and where:

from a in Addresses
from c in Companies
where (c.AddressId == a.Id || c.MailAddressId == a.Id || c.Id == a.CompanyId)
         &&  (c.Id == *CompanyId* && a.Name == *AddressName*)
select new{a.Id, a.Name}

Upvotes: 2

Related Questions