CODEWITHSUNDEEP
c#entity-framework
Grant
Grant

Reputation: 11366

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

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.

Upvotes: 3

Views: 1489

Answers (2)

Colin
Colin

Reputation: 22595

It sounds like you may be trying to implement a soft delete. There is a solution for that over here https://stackoverflow.com/a/18985828/150342

But that means the status field can only be changed using sql....

Maybe you could use inheritance? So you'd have ActiveClients and InActiveClients and HyperActiveClients all inheriting from an abstract BaseClient. Then instead of setting the status you convert it from one type to another.

EDIT Thinking about it, the conversion is not so simple. You can't just cast one to the other, you'd have to delete the ActiveClient and create a new InActiveClient and that might not be trivial due to foreign key constraints etc...

Upvotes: 0

Manoj
Manoj

Reputation: 634

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.

Upvotes: 3

Related Questions