Entity Framework: Filtering Navigation Properties

Question

I am working with Entity Framework - Code First, and i have the next context (an extraction at least):

public class TestContext : DbContext
{
    public DbSet<User> Users { get; set}
    public DbSet<Book> Books { get; set}
}

And in the user's class i have this navigation property:

public virtual Collection<Book> Books { get; set}

ergo, a user has many books. The problem is that i want to filter the books, but as i have like 500.000 books on my database, i can't afford bringing all the books to memory and filter them later. I need to execute the query against the database with the filter sentence.

When i do this:

// Doesn't matter how i get the user...
var allBooks = user.Books; // Here it brings all the books of the user
var activeBooks = allBooks.Where(n => n.Active);

I think you can see the problem... i want to add filters to the query before executing it... but i don't know how can i do that.

I would appreciate any advice too.

Thanks.

EDIT:

Another example with the explicit context, may be it clear things up...

IQueryable<Course> query = new TestContext().Set<User>(); // It doesn't run the query yet.
var a = query.Where(n => n.Active); // Here it runs the query!
var b = a.ToList(); // The items was on memory...

Answer 1

I implemented a solution which involved wrapping IDbSet and using it instead of the normal members of a context, like this:

public class MyContext : DbContext
{
    public IDbSet<MyEntity> Entities { get; set; }

    public MyContext()
    {
        this.Entities = new FilteredDbSet<MyEntity>(this, x => x.SomeProperty == 1);
    }
}

The FilteredDbSet class automatically adds the expression passed on the constructor to all queries for that entity. See my example at http://weblogs.asp.net/ricardoperes/filter-collections-automatically-with-entity-framework-code-first.

Answer 2

I personally don't see any problem with what you are doing. The list of books will not be loaded into memory until you GetEnumerator(), either explicitly or by invoking foreach etc..

var activeBooks = user.Books.Where(n => n.Active); //still iqueryable
var inMemory = activeBooks.ToList(); //executes iqueryable
//or
foreach(var book in activeBooks)
    {

    } 

Edit

you may want to read up on IQueryable http://msdn.microsoft.com/en-us/library/system.linq.iqueryable(v=vs.110).aspx

"The IQueryable interface inherits the IEnumerable interface so that if it represents a query, the results of that query can be enumerated. Enumeration causes the expression tree associated with an IQueryable object to be executed. The definition of "executing an expression tree" is specific to a query provider. For example, it may involve translating the expression tree to an appropriate query language for the underlying data source. Queries that do not return enumerable results are executed when the Execute method is called."

This means that in order to execute the query you must enumerate the enumerable.

IQueryable<Course> query = new TestContext().Set<User>(); // It doesn't run the query yet.

IQueryable<Course> a = query.Where(n => n.Active); // Not executed yet

IQueryable<Course> c = a.Where(n => n.Title.Contains("Science")); //still not executed

List<Course> b = a.ToList(); //Executes the query

Answer 3

Use filtered explicit loading to load the desired books:

dbContext.Entry(user).Collection(u => u.Books).Query().Where(b => b.Active).Load();