Using Entity Framework and where queries without creating lots of queries (avoiding N+1)

Question

I've been using a design pattern that after using the Entity Framework Profiler seems like it might be quite foolish.

I've been extended my entity classes to have properties that are filtered views of a collection associated with that entity. Like this:

public IEnumerable<Member> ActiveMembers
{
    get
    {
        return Members.Where(t => t.LeftDate == null);
    }
}

As I'm mainly interested in members who have not left the club this property was very useful and seemed to make sense.

However from running the EF Profiler I now know that this often leads to N+1 problems. If I loop through the members and also want to show their address then each request for an address results in an extra db query.

I know from this question I asked that I could modify my property to:

return Members.CreateSourceQuery().Include("Address")
              .Where(t => t.LeftClubDate == null);

This would get rid of the N+1 problem in this case, but I don't always want the address information and I might want to follow another navigation property from Member.

Ideally I want to be able to keep the flexibility of my filtered properties like ActiveMembers and be able to decide afterwards what properties to include in the query. Like this:

var membersToDisplay = ActiveMembers.Include("Address").ToList();

Is this possible, or do I need to rething my filtered properties idea?

Answer 1

No, it is not possible to call Include on IEnumerable. Include is feature of ObjectQuery / DbQuery. It is possible to call Include on IQueryable (with EFv4.1 or custom extension) but it still internally cast passed query to ObjectQuery or DbQuery and throws exception if cast cannot be done. You must redesign your application.

I don't always want the address information and I might want to follow another navigation property from Member.

You have to fill data based on your current needs or live with N+1 problem. For example you can use separate linq query:

var clubId = ActiveClub.Id;
var members = (from member in context.Members.Include("Address")
               where member.LeftDate == null and member.ClubId == clubId
               select member).ToList();