using Join with entity framework object query. Navigation property still shows all the entities of the joined entity

Question

My EF object query is:

from customer in MCDBContext.Customers
join customerCase in MCDBContext.CustomerCases on customer.CustomerID equals customerCase.CustomerID
join customerCasePhone in MCDBContext.CustomerCasePhones on customerCase.CustomerCaseID equals customerCasePhone.CustomerCaseID
**join customerCaseAddress in MCDBContext.CustomerCaseAddresses on customerCase.CustomerCaseID equals customerCaseAddress.CustomerCaseID**

where customer.CustomerPIN.Equals(pin, StringComparison.InvariantCultureIgnoreCase) &&
        customerCasePhone.PhoneNumber.Equals(phoneNumber, StringComparison.InvariantCultureIgnoreCase) &&
        **customerCaseAddress.AddressTypeID == 6** &&
        customerCase.IsActive == true && customerCasePhone.IsActive == true && customerCaseAddress.Active == true && customer.IsActive == true
select customer;

In the code:

var customers = getmethod() //calls the above object query method. 

now when I do customers.First().CustomerAddresses(); //Returns all the customer addresses, though I filtered it to do only of TypeID = 6 in my object query. Why so? I don't want to write the filter condition again. Any ideas?

Thanks.

Answer 1

Your query does not return all addresses per customer. It doesn't return any address at all. The addresses get loaded afterwards due to lazy loading when you access the navigation properties. That's a second query and lazy loading always returns all addresses.

If you want to get the desired result in a single database query you need a projection to load customer AND addresses in the select operation.

Leveraging your navigation properties (I guess, you have some, because of the tag under your question), it would look like this:

var result = MCDBContext.Customers
    .Where(customer => customer.IsActive && customer.CustomerPIN.Equals(
           pin, StringComparison.InvariantCultureIgnoreCase)
        && customer.CustomerCases.Any(ccase =>
               ccase.IsActive
            && ccase.CustomerCasePhones.Any(phone => 
                   phone.IsActive
                && phone.PhoneNumber.Equals(
                   phoneNumber, StringComparison.InvariantCultureIgnoreCase))
            && ccase.CustomerCaseAddresses.Any(address =>
                   address.IsActive
                && address.AddressTypeID == 6)))
    .Select(customer => new
    {
        Customer = customer,
        // you can also fetch here cases and phones, if you need them
        Addresses = customer.CustomerCases.Where(ccase => ccase.IsActive)
            .Select(ccase => ccase.CustomerCaseAddresses
               .Where(address => address.IsActive && address.AddressTypeID == 6))
    })
    .ToList();

This does not populate the addresses collection in the object tree of customer but only returns a list of anonymous objects: Each entry has the customer and the filtered addresses of this customer.