CODEWITHSUNDEEP
c#entity-framework
chris
chris

Reputation: 1216

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

I have three tables

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'.

Upvotes: 4

Views: 2232

Answers (2)

ocuenca
ocuenca

Reputation: 39326

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

Upvotes: 2

The Sharp Ninja
The Sharp Ninja

Reputation: 1041

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.

Upvotes: 3

Related Questions