Use WHERE clause on a subtable in LINQ

Question

Use WHERE clause on a subtable in LINQ

c# entity-framework-core 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.

Dave

Popular Answer

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.

Flater

View more on Stack Overflow

ilkerkaran · Accepted Answer · 7/25/2018 11:49:05 AM

Accepted Answer

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

ilkerkaran

Use WHERE clause on a subtable in LINQ

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related