LINQ To Entities and Lazy Loading

Question

In a controversial blog post today, Hackification pontificates on what appears to be a bug in the new LINQ To Entities framework:

Suppose I search for a customer:

var alice = data.Customers.First( c => c.Name == "Alice" );

Fine, that works nicely. Now let’s see if I can find one of her orders:

 var order = ( from o in alice.Orders
          where o.Item == "Item_Name"
          select o ).FirstOrDefault();

LINQ-to-SQL will find the child row. LINQ-to-Entities will silently return nothing.

Now let’s suppose I iterate through all orders in the database:

foreach( var order in data.Orders ) { 
Console.WriteLine( "Order: " + order.Item ); }

And now repeat my search:

var order = ( from o in alice.Orders
          where o.Item == "Item_Name"
          select o ).FirstOrDefault();

Wow! LINQ-to-Entities is suddenly telling me the child object exists, despite telling me earlier that it didn’t!

My initial reaction was that this had to be a bug, but after further consideration (and backed up by the ADO.NET Team), I realized that this behavior was caused by the Entity Framework not lazy loading the Orders subquery when Alice is pulled from the datacontext.

This is because order is a LINQ-To-Object query:

var order = ( from o in alice.Orders
      where o.Item == "Item_Name"
      select o ).FirstOrDefault();

And is not accessing the datacontext in any way, while his foreach loop:

 foreach( var order in data.Orders )

Is accessing the datacontext.

LINQ-To-SQL actually created lazy loaded properties for Orders, so that when accessed, would perform another query, LINQ to Entities leaves it up to you to manually retrieve related data.

Now, I'm not a big fan of ORM's, and this is precisly the reason. I've found that in order to have all the data you want ready at your fingertips, they repeatedly execute queries behind your back, for example, that linq-to-sql query above might run an additional query per row of Customers to get Orders.

However, the EF not doing this seems to majorly violate the principle of least surprise. While it is a technically correct way to do things (You should run a second query to retrieve orders, or retrieve everything from a view), it does not behave like you would expect from an ORM.

So, is this good framework design? Or is Microsoft over thinking this for us?

Answer 1

Having lost a few days to this very problem, I sympathize.

The "fault," if there is one, is that there's a reasonable tendency to expect that a layer of abstraction is going to insulate from these kinds of problems. Going from LINQ, to Entities, to the database layer, doubly so.

Having to switch from MS-SQL (using LingToSQL) to MySQL (using LinqToEntities), for instance, one would figure that the LINQ, at least, would be the same if not just to save from the cost of having to re-write program logic.

Having to litter code with .Load() and/or LINQ with .Include() simply because the persistence mechanism under the hood changed seems slightly disturbing, especially with a silent failure. The LINQ layer ought to at least behave consistently.

A number of ORM frameworks use a proxy object to dynamically load the lazy object transparently, rather than just return null, though I would have been happy with a collection-not-loaded exception.

I tend not to buy into the they-did-it-deliberately-for-your-benefit excuse; other ORM frameworks let you annotate whether you want eager or lazy-loading as needed. The same could be done here.

Answer 2

Jon,

I've been playing with linq to entities also. It's got a long way to go before it catches up with linq to SQL. I've had to use linq to entities for the Table per Type Inheritance stuff. I found a good article recently which explains the whole 1 company 2 different ORM technologies thing here.

However you can do lazy loading, in a way, by doing this:

// Lazy Load Orders 
var alice2 = data.Customers.First(c => c.Name == "Alice");

// Should Load the Orders
if (!alice2.Orders.IsLoaded)
    alice2.Orders.Load();

or you could just include the Orders in the original query:

// Include Orders in original query
var alice = data.Customers.Include("Orders").First(c => c.Name == "Alice");

// Should already be loaded
if (!alice.Orders.IsLoaded)
    alice.Orders.Load();

Hope it helps.

Dave

Answer 3

So, is this good framework design? Or is Microsoft over thinking this for us?

Well lets analyse that - all the thinking that Microsoft does so we don't have to really makes us lazier programmers. But in general, it does make us more productive (for the most part). So are they overthinking or are they just thinking for us?

Answer 4

I don't know much about ORMs, but as a user of LinqToSql and LinqToEntities I would hope that when you try to query Orders for Alice it does the extra query for you when you make the linq query (as opposed to not querying anything or querying everything for every row).

It seems natural to expect

from o in alice.Orders where o.Item == "Item_Name" select o

to work given that's one of the reasons people use ORM's in the first place (to simplify data access).

The more I read about LinqToEntities the more I think LinqToSql fulfills most developers needs adequately. I usually just need a one-to-one mappingn of tables.

Answer 5

If LINQ-to-Sql and LINQ-to-Entities came from two different companies, it would be an acceptable difference - there's no law stating that all LINQ-To-Whatevers have to be implemented the same way.

However, they both come from Microsoft - and we shouldn't need intimate knowledge of their internal development teams and processes to know how to use two different things that, on their face, look exactly the same.

ORMs have their place, and do indeed fill a gap for people trying to get things done, but the ORM uses must know exactly how their ORM gets things done - treating it like an impenetrable black box will only lead you to trouble.

Answer 6

Even though you shouldn't have to know about Microsoft's internal development teams and processes, fact of the matter is that these two technologies are two completely different beasts.

The design decision for LINQ to SQL was, for simplicity's sake, to implicitly lazy-load collections. The ADO.NET Entity Framework team didn't want to execute queries without the user knowing so they designed the API to be explicitly-loaded for the first release.

LINQ to SQL has been handed over to ADO.NET team and so you may see a consolidation of APIs in the future, or LINQ to SQL get folded into the Entity Framework, or you may see LINQ to SQL atrophy from neglect and eventually become deprecated.