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

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

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);

edited May 23 '17 at 10:34

Community

1
1

answered Nov 23 '11 at 16:20

Adam Rackis

82,527
56
270
393

Thanks @Adam Rackis. But surely if I go Table1.Table2.Where(x => x.Id == Id) it will not load everything in Table2. My understanding would be becuase under the covers it is IQueryable not IEnumerable then it stashes everything to send to the server in one go - triggered when you realise the query i.e. with ToList(). Otherwise the navigation properties are unusable in EF. – Crab Bucket Nov 23 '11 at 16:27
Yes, you are right. Navigation properties are not IQueryable. The out-of-the box behavior is probably best for most cases. If you're needing more fine-grained control, maybe NH would be a better ORM for you? (this is coming from a HUGE EF fan, btw) – Adam Rackis Nov 23 '11 at 16:30
@Tim, also see my edit. This may be possible using DbContext, but still not possible by just plowing through the objects navigation property – Adam Rackis Nov 23 '11 at 16:31
This looks really good. We are on EF 4 but it looks like this might be worth an upgrade to EF 4.1. Our problem here is that we are filtering on two params - one implicitly through the navigation property and one through the query - and we have gone the wrong way through the navigation i.e. we've chosen the navigation that ends up with the biggest set by quite some way. I was convinced it was the view – Crab Bucket Nov 23 '11 at 16:54
2

@Tim: You don't need to upgrade. EFv4 supports this as well but the [syntax is different](http://stackoverflow.com/questions/6658459/efficient-way-of-checking-if-many-to-many-relationship-exists-in-ef4-1/6661380#6661380) (worse) and it has some pitfalls because you must explicitly turn on/off lazy loading to avoid huge query. – Ladislav Mrnka Nov 23 '11 at 17:01
@Ladislav - ahh - createSourceQuery - forgot about that. Thank you. – Adam Rackis Nov 23 '11 at 17:10

score 1 · Answer 2 · answered Nov 23 '11 at 18:03

I've had some issues with the way that EntityFramework generates SQL so first of all I will suggest that you use LinqPad and one or more of the following: EntityFramework profiler (paid for software), SQL Profiler (assuming you are using SQL Server) and/or EFTracingProvider

I've had issues where the same table was inner joined several times on some queries so having those tools generally helps find out what's causing the issue.

The things that I've tried that have often made some queries run faster:

Writing a full Linq Query rather than using Lambda expressions: they are often easier to read and they look a lot more like sql so it's easier to see the relationship between your code and the generated sql

and

EntitySet.Include(x=>x.Property)

This tells Linq2Entities to include the property in the query

Entity Framework - Excessive data loads with Views

2 Answers2