How to place filters or conditions on navigation properties in Entity Framework

Question

I have a table in my sql database called 'Clients' and in that table is a field called status. It can contain 2 values, 'A' for active or 'I' for inactive. Meanwhile in my mvc web application using Entity Framework v5 I have implemented the repository pattern with a specific ClientRepository.

Whenever I make a call to the ClientRepository there is a predefined filter that ensures all queries are filtered and that only status 'A' records are returned.. and all is well.

The problem that I am facing now is when I use LINQ to query a table that is linked to Clients, eg - ClientOrders and then access the navigation property called Clients. When I do so it retrieves all clients with any 'status' including 'I'.

Does anyone know if there is a way to configure E.F. in the designer or context to set conditions on navigation properties to satisfy my requirements such that only status 'A' will be returned?

Note that this is one example of many cases in my application that use 'status' as a record indicator and there are multiple navigation properties I will need to apply a fix to.

Thanks.

Answer 1

You can achieve this with a LINQ query. Here is an example of doing this:

var clientOrders = _context.ClientOrders    
    .Select(item => new {
        ClientOrders = item,
        Clients = item.Clients.Where(q => q.Status == "A")
    }).ToList();

This will return a list of the new anonymous type. You can convert anonymous structure to ClientOrders after you execute this query.