Use WHERE clause on a subtable in LINQ

Question

In my Customer model, I've got

public virtual ICollection<AddressModel> AddressIDs { get; set; }

which references AddressModel to give me a one-to-many relationship between customers and their addresses.

I've got a search function which uses

var CustomerList = _context.Customers
    .Where(ps => ps.Surname.Contains(surnameToSearchFor))

to limit the returned dataset by surname.

I'm trying to add the ability to search for postcodes in the address. Following various links, this works in Visual Studio but breaks on execution

CustomerList = CustomerList
    .Include(ps => ps.AddressIDs
                     .Where(a => a.Postcode == postcodeToSearchFor));

with the error

InvalidOperationException: The property expression 'ps => {from AddressModel a in ps.AddressIDs where ([a].Postcode == __p_0) select [a]}' is not valid. The expression should represent a property access: 't => t.MyProperty

How do I add a Where clause to my LINQ on the sub table?

Edit To whoever suggested Multiple WHERE clause in Linq as the answer, that question clearly is relating to a single table, whereas I clearly asked about sub tables.

These is no way to do it with built in methods. Looks like it is known problem and there is an extention libraries for that: [Entity Framework Plus | IncludeFilter](http://entityframework-plus.net/query-include-filter) — Renatas M., Jul 25 '18 at 11:54

score 5 · Accepted Answer · answered Jul 25 '18 at 11:49

5

You can't use where statement in Include. You can get what you want with single linq query like this:

var CustomerList = _context.Customers.Where(ps =>
    ps.Surname.Contains(surnameToSearchFor) 
    && ps.AddressIDs.Any(ad => ad.Postcode == postcodeToSearchFor ));

answered Jul 25 '18 at 11:49

ilkerkaran

4,214
3
27
42

1

Note that this does not filter the included list. It will give you all customers who have at least one address in the postcode, but then it will load **all** addresses of the customer regardless of their postcode. – Flater Jul 25 '18 at 11:57
@Flatter 5, Without Include keyword, it does not load all addresses. Actually, it does not load any address yet (Assuming op uses eager loading option). However your way of thinking actually right. – ilkerkaran Jul 25 '18 at 12:03
I missed the missing `Include()`, you're right. I somewhat assume that OP wants to load the addresses. I wrote an answer under that assumption, but if OP does not want the addresses, your answer is the better one. – Flater Jul 25 '18 at 12:09

score 3 · Answer 2 · answered Jul 25 '18 at 12:07

As the other answer (by Progressive) already mentioned, you're not able to filter on an Include.

The other answer (by Progressive) might be a solution for you, or it might not be. It will give you all customers who have at least one address in the postcode, but then it will load all addresses of the customer (including those in a different postcode).

This answer is written in case you only wish to retrieve the addresses for the postcode, which I suspect is the case. If you only wish to retrieve customers and only filter on their addresses (but not load the addresses), the other answer (by Progressive) is the solution.

As I said, you can't do it via an include. However, there are other solutions:

1. Look up addresses and include customers.

Instead of looking up customers and including their addresses.

 var addresses = _context.Addresses
                         .Include(a => a.Customer)
                         .Where(a =>
                              a.Postcode == postcodeToSearchFor
                              &&
                              a.Customer.Surname.Contains(surnameToSearchFor))
                     .ToList();

You can still get a list of customers this way:

var customer = addresses.Select(a => a.Customer).Distinct();

As a general rule of thumb, always start your query from the child and include its parents, not the other way around. It doesn't matter in some cases, but in your particular case, it does matter as you want to avoid implicitly loading all children.

2. Explicitly define your result set

In other words, use a Select().

This give you more control in case you have really specific expectations of the outcome, but it's more verbose and (imo) not as good as the other solution. Only use it if the first solution doesn't work for you.

_context.Customers.Where(ps =>
                             ps.Surname.Contains(surnameToSearchFor) 
                             && 
                             ps.AddressIDs.Any(ad => ad.Postcode == postcodeToSearchFor))
                  .Select(ps => new
                                {
                                    Customer = ps,
                                    Addresses = ps.AddressIDs.Where(ad => ad.Postcode == postcodeToSearchFor))
                                })
                  .ToList();

Notice that you don't need the Include() here. Include() configures implicit loading behavior, but your Select() loads data explicitly.

score 1 · Answer 3 · answered Jul 25 '18 at 12:11

1

Try this:

var CustomerList = _context.Customers.Where(ps => ps.Surname.Contains("surnameToSearchFor")).Select(ps => new
{
   Surname = ps.Name,
   AddressIDs = ps.AddressIDs.Where(a => a.PostCode == postcodeToSearchFor)
 });

answered Jul 25 '18 at 12:11

Sharaz

23
1
7

You will also load customers who don't have any address in `postcodeToSearchFor`. – Flater Jul 25 '18 at 12:13
that can be resolved by adding this and condition: ps.Surname.Contains("surnameToSearchFor") && ps.AddressIDs.Count > 0 – Sharaz Jul 25 '18 at 12:15
But it all depends on what you want, if you want customers who can have 0 AddressIds, you will need to remove the second condition. – Sharaz Jul 25 '18 at 12:17
`&& ps.AddressIDs.Count > 0` would filter out customers who don't have **any** address. It would not filter out customers who have addresses, but no addresses in `postcodeToSearchFor`. – Flater Jul 25 '18 at 12:34
ps.Surname.Contains("surnameToSearchFor") && ps.AddressIDs.Where( a => a.PostCode == postcodeToSearchFor).Count > 0 :) – Sharaz Jul 25 '18 at 13:09

score 0 · Answer 4 · answered Jul 25 '18 at 13:59

I need the forename and surname from the Customer model who've got a specific address, so based on Progressive's answer I've used this

var CustomerList = CustomerList.Where(ps => ps.AddressIDs
      .Any(a => a.Postcode.Contains(postcodeToSearchFor)));

which brings back any Customer with any part of the postcode but doesn't bring back anyone with a blank or null postcode.

Use WHERE clause on a subtable in LINQ

4 Answers4