Entity Framework 4.1 Code First - Controlling Eager Loading

Question

Is it possible to control eager loading of child objects.

If I had a parent class that has 20,000 child objects and I only wanted to retrieve a subset of those child objects is it possible?

How would I write the query to do that if it is?

For Example:

I have an Entity called Book which has a number of related Reviews:

public class Book {
    public int BookId { get; set; }
    public string BookName { get; set; }
    public ICollection<Review> Reviews { get; set; }
}

public class Review { 
    public int ReviewId { get; set; }
    public int Score { get; set; }
    public Book Book { get; set; }
}

I want to do something like:

var bookWithFirstTwentyReviews = db.Books.Where(b => b.BookId == 1).Include("Reviews").FirstOrDefault();

But I only want to include 20 reviews, not all 20,000

do you mean selective loading of a subset of a child collection or completely separate child objects? — BrokenGlass, Sep 12 '11 at 17:42
@BrokenGlass: Why did you delete your answer? I'm pretty sure it was a working approach. — Slauma, Sep 13 '11 at 00:01
@Slauma: OP said it wasn't working at all and I didn't have time to test it — BrokenGlass, Sep 13 '11 at 02:13
@BrokenGlass: I've tested your solution and it works. I remember that you had an `.ToList()` operator in the projection into the anonymous object. This must be removed ("can't be translated into store expression, bla bla"), but other than that, the solution was fine. — Slauma, Sep 13 '11 at 12:09

score 3 · Accepted Answer · edited May 23 '17 at 12:29

Include doesn't allow any filtering or sorting. One option is to use explicite loading:

// load book without Reviews (1st roundtrip to DB)
var book = context.Books.Where(b => b.Id == id).SingleOrDefault();

// then load a filtered collection of Reviews (2nd roundtrip to DB)
if (book != null)
    context.Entry(book).Collection(b => b.Reviews).Query().Take(20).Load();

It requires two roundtrips to the database though (which is not necessarily bad, performancewise, because eager loading on the other hand comes with potentially lots of duplicated data).

The other approach is to use a projection and leverage relationship span in the context. This was the solution shown by @BrokenGlass (deleted now).

Entity Framework 4.1 Code First - Controlling Eager Loading

1 Answers1

Linked