LINQ To Entities and Lazy Loading

linq-to-entitieslinq-to-sql

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?

Best Answer

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

Related Topic