CODEWITHSUNDEEP
c#entity-frameworkiqueryable
Pertinent Observer
Pertinent Observer

Reputation: 311

Add where clause in Include statement for Queryable object

Suppose I have the following two tables

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

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

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?

Upvotes: 5

Views: 397

Answers (2)

believe me
believe me

Reputation: 908

Suppose you have an entity similar to this

public class A
{
    public int Id {get;set;}
    public ICollection<B> Bs {get;set;}
}

public class B
{
    public bool IsEnabled {get;set;}
}

So when you do the query since you have the ICollection to the Bs it should work with no runtime errors.

Upvotes: 0

Richard
Richard

Reputation: 30618

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

Upvotes: 1

Related Questions