Entity Framework - Excessive data loads with Views

Question

We are having a problem navigating between entities one of which is based on a view. The problem is when we go

TableEntity.ViewEntity.Where(x => x.Id == Id).FirstOrDefault())

In the background it is loading all records in the view which is not what we want or expect.

However when we go

_objectContext.TableEntityView
    .Where(x => x.TableObjectId == TableObjectId && x.Id == Id)

Then it just loads up the one row which is what we are expecting

In short using the navigation properties causes a massive data load – it’s like the query is being realised early.

We are using EF 4 with SQL 2005 database. The views are used to provide aggregated information which EF couldn’t easily do without big data loads (ironically). We have manually constructed 1: Many associations between the views.

Why then do we get the large data load in the first instance but not the second?

Many thanks for all/any help

Answer 1

That's how navigation collections work in EF: accessing the collection loads all entities, and any linq queries you run thereafter simply query against the objects in memory. I don't think there's anything you can do about it short of a custom query like you've already done.

FWIW I'm told NHibernate supports more fine-grained navigation loads, but that feature has yet to make its way into Entity Framework.

EDIT

This answer from Ladislav Mrnka shows a possible solution to your problem from the CTP days. Not sure if anything has changed since then. It uses the DbContext, so you still won't be able to just plow through the navigation property, but it's probably as close as you're going to get.

int count = context.Entry(myAccount)
                   .Collection(a =>  a.Orders).Query().Count();`

or for your case, I'm guessing it would be

TableEntityView obj = context.Entry(TableEntity)
                             .Collection(a => a.ViewEntity)
                             .Query().FirstOrDefault(x => x.Id == Id);