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.
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 ));
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.