when using code first, accessing association does not account for .Take(x)

2 entities: Member and Comment

Member has an ICollection<Comment> Comments

Whenever I use member.Comments.Take(x) EF produces a query that gets all the comments from database. Is it supposed to be like that?

Is it because property is I开发者_开发问答Collection? Is there a way to tell EF to factor in my Take(x) or should i refactor my code to use context.Comments.Where(c=>c.MemberId==member.Id).Take(x) and live with it?

---

As described by @J. Tihon it is how EF works. When accessing lazy loaded property EF will always load the whole collection and any Linq expression is evaluated on the loaded collection. If you want to avoid that you must use the query as you described but the result of the query will not be loaded into your navigation property. To solve this you can use explicit loading instead of lazy loading:

context.Entry(member)
       .Collection(m => m.Comments)
       .Query()
       .OrderBy(...) // Take requires some sorting
       .Take(2)
       .Load();

This should fill your Comments property with two comments.

---

The proxy classes generated by EF only provide lazy-loading for navigation properties, but they do not evaluate queries. Once you accessed the member.Comments property, the Comment-entities are loaded from the database and your query is applied in memory. To avoid this, you must get your comments in a query that is directly executed on the object-set (like the example you've already gave).

I believe this is by design, since you would have to return an IQueryable from the navigation property in order for the EF to intercept access to this property, but I suppose this isn't covered aswell.

You've already described a way to handle this, although it isn't pretty. Another option would be to somehow tell EF to partially load the property when you make the original query for the Member-object. I will look into that, but I can already think of one or two thinks that might go wrong with that approach.

Edit After some research and trial and error I couldn't come up with another approach, that could be executed directly on the DbSet<Member> rather than DbSet<Comment> and returns a Member object. I is possible using an anonymous object:

var query = from m in catalog.Members
            select new
            {
                Id = m.Id,
                Name = m.Name,
                Comments = m.Comments.Take(1)
            };

Which could then be translated into a Member-object in memory, but of course it wouldn't be connected to the context in anyway (=no change tracking). In the sample query above I cannot create an instance of Member instead of an anonymous type, because EF can only create non-complex types (I'm guessing because the context knows that "Member" is an entity).