Entity Framework Query with multiple join conditions

Question

Edited I have tables Customers, Sites, Buildings and Addresses.

Every customer has zero or more (one?) sites, every site is the site of exactly one customer, namely the site that the foreign key Site.CustomerId refers to.

Similarly, every site has zero or more buildings, every building is on exactly one site, namely the site that the foreign key Building.SiteId refers to.

And finally: every customer / site / building has exactly one address, namely the address that the foreign key Customer.CustomerPhysicalAddressId, Site.AddressId, Building.BuildingAddressId refer to.

I also have a string searchText

I want the ids of all customers that have at least one of the following:

• a CustomerName that is like searchText
• at least one SiteName that is like searchText
• at least one BuildingName that is like searchText
• a PhysicalAddress like searchText
• at least one SiteAddress of all its Sites that is like searchText
• at least one BuildingAddress of all its Buildings that is like searchText

For the above requirement I have this SQL query logic

SELECT DISTINCT c.customerID 
FROM Customer AS c
LEFT OUTER JOIN Site AS s ON s.customerId = c.customerID
LEFT OUTER JOIN Building AS b ON s.Id = b.siteId
LEFT OUTER JOIN Address AS A ON A.addressId = c.customerPhysicalAddressID 
                             OR A.addressId = s.AddressId 
                             OR A.addressId = b.buildingAddressId
WHERE 
    c.customerName LIKE '%searchText%' 
    OR c.SiteName LIKE '%searchText%' 
    OR b.buildingName LIKE '%searchText%' 
    OR A.Street LIKE '%searchText%'

The problem arises in the controller class while writing the linq query.

My Linq query is written as follows

if (!string.IsNullOrEmpty(searchText))
{
    var resultQuery = from customer in this.DatabaseContext.Customers
                      join site in this.DatabaseContext.Sites
                           on customer.customerID equals site.CustomerId into customer_site_group
                      from customer_site in customer_site_group.DefaultIfEmpty()
                      join building in this.DatabaseContext.Buildings
                           on customer_site.Id equals building.siteId into site_building_group
                      from site_building in site_building_group.DefaultIfEmpty()
                      join A in this.DatabaseContext.Addresses
                             on new
                               {
                                   key1 = customer.customerPhysicalAddressID,
                                   key2 = customer_site.AddressId,
                                   key3 = site_building.buildingAddressID
                               }
                               equals new
                               {
                                    key1 = A.addressID ||
                                    key2 = A.addressID ||
                                    key3 = A.addressID
                               } into Address_site_building
                      where (customer.customerName.Contains(searchText) ||
                             customer_site.siteName.Contains(searchText) ||
                             site_building.buildingName.Contains(searchText) ||
                             A.street.Contains(searchText))
                      select new
                             {
                                   customerID = customer.customerID
                             };
}

In result query I just want to have customer Id satisfying the above conditions. The linq query is working fine till Addresses entity is introduced. Facing to write multiple on conditions, LinqPad shows an error

The type of one of the expression in the join clause is incorrect. Type reference failed in the call to GroupJoin

I am new to EF and linq - just trying and understanding it.

Thanks for any valuable comments and answers.

Answer 1

The answer will probably be evident if you see that the SQL query can be rewritten as

SELECT DISTINCT c.customerID 
FROM Customer AS c
LEFT OUTER JOIN Site AS s ON s.customerId = c.customerID
LEFT OUTER JOIN Building AS b ON s.Id = b.siteId
, Address AS A
WHERE 
    (c.customerName LIKE '%searchText%' 
    OR c.SiteName LIKE '%searchText%' 
    OR b.buildingName LIKE '%searchText%' 
    OR A.Street LIKE '%searchText%')
AND (A.addressId = c.customerPhysicalAddressID 
    OR A.addressId = s.AddressId
    OR A.addressId = b.buildingAddressId)

I.e. the join turned into a WHERE clause. Then the LINQ translation becomes something like

from customer in this.DatabaseContext.Customers
join site in this.DatabaseContext.Sites
    on customer.customerID equals site.CustomerId into customer_site_group
from customer_site in customer_site_group.DefaultIfEmpty()
join building in this.DatabaseContext.Buildings
    on customer_site.Id equals building.siteId into site_building_group
from site_building in site_building_group.DefaultIfEmpty()
from A in this.DatabaseContext.Addresses
where (customer.customerPhysicalAddressID = A.addressID
       || customer_site.AddressId = A.addressID
       || site_building.buildingAddressID = A.addressID)
where (customer.customerName.Contains(searchText) ||
     customer_site.siteName.Contains(searchText) ||
     site_building.buildingName.Contains(searchText) ||
     A.street.Contains(searchText))
select new
{
    customerID = customer.customerID
};

General advice (see my comment): try to remove the joins from your LINQ query by introducing navigation properties.