Filtering Entities with foreign key value

Entity Framework 4.0 supports two two types of association, foreign key association and independent association. Foreign key association which is new allows you to modify/filter an entity using either navigation property or foreign key column exposed on the entity.

In the model below, we have a customer entity with 0..1 association with Address entity. meaning for a given customer we may not have an address and therefore AddressId on the customer table is marked as nullable.

To only retrieve customers that have an address we can either write query by checking  Address navigation property is not null or  checking AddressId foreign key value is not null. Code below shows both approaches.

Although both linq queries give us the right result, they generate slightly different sql query. In the case of applying a filter using navigation property, EF adds extra stuff which is not needed. Code below shows the sql query for linq query, qry1.

When testing for null against the foreign key value, the query is as clean as it possibly gets with no extra noise.

If you have a requirement where you want to filter entities based on foreign key value, try using foreign key value instead of testing if navigation property is null because that generates better sql. If you do not have access to foreign key value because you are using independent association, you can traverse the navigation property and test for primary key property being not null. For instance in the case of our above model, we can test if cust.Address.AddressId != null and this would give us clean sql.