How can I add a where clause to an in included entity in EF?

Question

I have three tables

Person Table (Generic person table)
Customer Table (every customer is a person)
Address Table (every customer has an address)

I need to query the database from entity framework for a match on the person's name and city, state. This is what I have but it doesn't work unless I remove the state and city from the where clause

var customer = db.tbl_Person
    .Include(t => t.tbl_Customer.tbl_Address)
    .Where(t => t.VendorID == person.VendorID &&
                t.FirstName == person.FirstName &&
                t.LastName == person.LastName &&
                t.tbl_Customer.tbl_Address.State == address.State &&
                t.tbl_Customer.tbl_Address.City == address.City).ToList();

Any help would be appreciate - I'm still fairly new to EF. As stated in my comments below, the error I get is

Additional information: Unable to cast object of type 'System.Linq.Expressions.FieldExpression' to type 'System.Linq.Expressions.ParameterExpression'.

The Sharp Ninja · Accepted Answer · 2015-12-04T17:52:33.097

3

var customer = db.tbl_Person
    .Include(t => t.tbl_Customer.tbl_Address)
    .Where(t => t.VendorID == person.VendorID &&
                t.FirstName == person.FirstName &&
                t.LastName == person.LastName)
    .ToList()
    .Where(t => t.tbl_Customer?.tbl_Address != null &&
                t.tbl_Customer.tbl_Address.State == address.State &&
                t.tbl_Customer.tbl_Address.City == address.City).ToList();

Breaking up the where should ensure that the secondary part of the query is called. Also, you need to make sure that the two intervening records are not null.

EDIT: Added the ToList() between the wheres. Why? Because the error in the comment reflected that Linq is attempting to use a non-parameterized field as a parameterized field to dynamically build the query. By placing the .ToList() in between, it forces the query to run on the first subset, and then filter that set by the State and City.

edited Dec 04 '15 at 17:52

answered Dec 04 '15 at 17:16

The Sharp Ninja

1,041
9
18

1

still getting the same error when I try to filter out the address `Additional information: Unable to cast object of type 'System.Linq.Expressions.FieldExpression' to type 'System.Linq.Expressions.ParameterExpression'.` @octavioccl this is the error i receive for answer as well – chris Dec 04 '15 at 17:48
1

@chris, I don't know if your Linq to Entities provider supports to translate null-conditional to a proper expression. Try removing the first condition (`t.tbl_Customer?.tbl_Address != null`). – ocuenca Dec 04 '15 at 18:06
1

awesome that worked after I added the additional ToList to receive the initial data fetch. @octavioccl the null check this way worked to my surprise. Thanks for the help guys! – chris Dec 04 '15 at 18:14
2

You're welcome @chris, that's because you're working with linq to objects instead linq to entities – ocuenca Dec 04 '15 at 18:17
1

Interesting @octavioccl - quick search led me to this stack question regarding the difference. http://stackoverflow.com/questions/7192040/linq-to-entities-vs-linq-to-objects-are-they-the-same – chris Dec 04 '15 at 18:32

ocuenca · Answer 2 · 2016-01-27T17:56:45.270

I think the way of including related properties has changed a little bit on EF 7. Try this:

var customer = db.tbl_Person
    .Include(t => t.tbl_Customer).ThenInclude(c=>c.tbl_Address)
    .Where(...).ToList();

You need to use ThenInclude method to include your second level. Check this reference.

Update

I guess you are checking if tbl_Customer is not null because you have a conditional relationship between Person and Customer. Another way to check if you have a Customer related is using the FK property. For example if the type of CustomerId is int and a Customer always is related with an Address (a required relationship) , you can do the following:

var customer = db.tbl_Person
    .Include(t => t.tbl_Customer.tbl_Address)
    .Where(t => t.VendorID == person.VendorID &&
                t.FirstName == person.FirstName &&
                t.LastName == person.LastName &&
                t.tbl_CustomerId != 0 && // compare with default value of your FK property
                t.tbl_Customer.tbl_Address.State == address.State &&
                t.tbl_Customer.tbl_Address.City == address.City).ToList();

Hi @chris I updated my answer with another possible solution that came to my mind now. This way you can avoid to load unnecessary customers into memory because all the query is executed on server side. — ocuenca, Dec 04 '15 at 19:08

How can I add a where clause to an in included entity in EF?

2 Answers2

Update