Add where clause in Include statement for Queryable object

Question

Suppose I have the following two tables

• A
• B
• C

A, B and C tables have a boolean "IsEnabled" column.

There is 1 to many relationship between the tables as follows:

• A->B
• B->C

I am using Entity framework to query the table and the programming is in C#. Suppose, I need all the columns in A, I do the following:

var query = _context.A; query.where( <where clause> )

If I need to include the columns of B to prevent lazy loading,

query.Include ( s => s.B );

The question is, how do I include the columns of B by adding a where clause to select only rows that have IsEnabled = 1? I am looking for something like:

query.Include ( s => s.B.where ( k => k.IsEnabled = 1 )) ( This does not work and throws a runtime exception)

If we can get the above question, I want to include the columns of C too of rows that is IsEnabled = 1 for both B and C. Is this possible?

Answer 1

Include is used to eagerly load relationships, but unfortunately Entity Framework does not support filtering these collections server-side, with either lazy or eager loading.

You've got two choices - do the filtering client-side, or rearrange your queries to make the most efficient use (e.g. if you're after C, select C.Where(c => c.IsEnabled && c.B.IsEnabled && c.B.A.IsEnabled)).