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.

Answer 2

So you have tables of Customers, Sites, Buildings and Addresses.

Every Customer has zero or more (one?) Site, 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 have exactly one Address, namely the Address that the foreign key Customer.CustomerPhysicalAddressId, Site.AddressId, Building.BuildingAddressId refer to.

You also have a string searchText.

You 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 his Sites that is like searchText
• at least one BuildingAddress of all his Buildings that is like searchText.

My advice would be, to get from every Customer his Id, and a sequence containing the following strings:

• the name of the Customer
• his physical address
• the names of all his Sites and Buildings
• the addresses of all his Sites and Buildings

The result is a sequence of [CustomerId, sequence of strings]. You only want to keep those CustomerIds, where at least one string in the "sequence of strings" is like searchText.

To create the [CustomerId, sequence of strings] combination is not difficult. You get problems when trying to implement "like searchText".

Let's first create the combinations.

Whenever you have "items with their subitems", and you want to treat them as one sequence of items, consider to use one of the overloads of Queryable.SelectMany.

var result = dbContext.Customers.SelectMany(customer => customer.Sites,

// parameter resultSelector: take every Customer with its Site to create one new:
(customer, sitesOfThisCustomer) => new
{
    Id = customer.Id,

    // the searchTexts: the customer name, his physical address
    // the names and address of of all his Sites
    // and the names and addresses of all the building of each side (inner SelectMany)
    SearchTexts = new string[] {customer.CustomerName, customer.PhysicalAddress}

    .Concat (sitesOfThisCustomer.SelectMany(site => site.Buildings,
    (site, buildingsOfThisSite) => new string[] {site.SiteName, site.SiteAddress}
        .Concat(buildingsOfThisSite.SelectMany(building => new string[]
            {building.BuildingName, building.BuildingAddress})));

I'm not sure whether the new string[] {...} works with an IQueryable, if not, think of another method of how to make an enumerable sequence with the name and address (Enumerable.Repeat? with repeat count 1?)

So now you have per customer the Id and one big sequence of names and addresses of the Customer, his Sites and the Buildings on these Sites. All you have to do is add a .Where for the Like searchText. As far as I know, standard LINQ doesn't have this, but maybe you can do something like:

.Where(customeWithSearchTexts => customerWithSearchTexts.SearchTexts
    .Any(text => text.StartsWith(searchText));

In the solution above, I used the virtual ICollection<...> that you see in entity framework. If you can't use that, because your classes don't have this, you'll have to do the groupjoin yourself:

var result = customers.SelectMany(

    // the Sites of this customre
    customer => dbContext.Sites.Where(site.CustomerId == customer.Id),

    // resultSelector:
    (customer, sitesOfThisCustomer) => ...

        // inner selectmany
        site.SelectMany(dbContext.Buildings.Where(building.SiteId == site.Id),

        ...