Partager via

Migrating from LINQ to SQL to Entity Framework: Eager Loading

  • Article

Since the release of the Entity Framework, we have received a number of requests for details and best practices from customers who are looking at migrating their LINQ to SQL based applications to the Entity Framework.  In order to address this topic, we are beginning a new series of blogs posts that will look at various aspects of migration.

This first post in the series covers Eager Loading in LINQ to SQL, and the steps for migrating to equivalent constructs in the Entity Framework in .NET 3.5 SP1. Subsequent posts will cover Deferred Loading, LINQ specifics, concurrency, mapping, stored procedure support, and other topics. As we continue to approach the next release of the Entity Framework we will revisit how some of the new features aid in migration.

What is Eager Loading?

Eager, or immediate, loading occurs when you query for an object and all of the related objects are also returned. One of the major differences in how LINQ to SQL and the Entity Framework implement eager loading is that LINQ to SQL allows eager loading behavior to be specified at the context level, and the Entity Framework supports it at the query level.

LINQ to SQL allows you to define the eager loading behavior at the Context level. The DataLoadOptions class allows you to define the load behavior and attach it to a context. This defines the eager loading behavior for all entities that are fetched for that particular DataContext instance. For more information, see Deferred versus Immediate Loading (LINQ to SQL).

 DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<Customer>(c => c.Order);
db.LoadOptions = dataLoadOptions;

List<Customer> customers = db.Customer.ToList();

The Entity Framework allows you to define eager loading at the query level by using the Include method. For more information, see Shaping Query Results.

 List<Customer> customers = db.Customer.Include("Order").ToList();

The scope of eager loading differs between LINQ to SQL and the Entity Framework. LINQ to SQL supports per-context eager loading options and the Entity Framework supports per-query eager loading options.

Using LINQ to SQL for cascading eager loading spanning multiple entity types

In LINQ to SQL, you can define eager loading for any type of entity that is passed with the LoadWith method. For example, you can specify EntitySets that are directly available on an entity as an argument. For each EntitySet that is to be eager loaded, declare a new LoadWith clause and include it as a part of DataLoadOptions.

If this is done for a chain of hierarchy as shown in the below example, Customer ->Loadwith ->Orders->Loadwith ->OrderDetails, we can eagerly load multiple levels in the object graph.

 DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<Customer>(c => c.Order);
dataLoadOptions.LoadWith<Order>(c => c.OrderDetail);
db.LoadOptions = dataLoadOptions;

List<Customer> customers = db.Customer.ToList();

Cascading Eager Loading in Entity Framework

The Include method allows multiple hierarchies of the EntityCollection to be denoted with dot (.) notation.

 

 List<Customer> customers = 
           db.Customer.Include("Order.OrderDetail").ToList();

In the Entity Framework, a single call of the Include method can eagerly load the multilevel hierarchy in an EntityCollection, per query.

Eager Loading of multiple relationships in LINQ to SQL

In LINQ to SQL, multiple EntitySets can be eagerly loaded with an entity by adding each one of them using the LoadWith method of the DataLoadOptions class and attaching the DataLoadOptions object to the DataContext.

 DataLoadOptions dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<Product>(c => c.OrderDetail);
dataLoadOptions.LoadWith<Product>(c => c.Supplier);
db.LoadOptions = dataLoadOptions;

Product firstProduct = db.Product.First();

Eager Loading of multiple relationships in Entity Framework

In the Entity Framework, you can use the Include method multiple times in a query to eager load multiple EntityCollections.

 Product firstProduct = 
       db.Product.Include("OrderDetail").Include("Supplier").First();

LINQ to SQL keeps multiple LoadWith clauses in an array within DataLoadOptions. The Entity Framework allows you to add multiple Include methods on an entity for a particular query.

In our next post, will look at Deferred/Lazy Loading. Stay tuned and let us know what you think!

- The ADO.NET Team

Comments

  • Anonymous
    October 07, 2008
    PingBack from http://www.easycoded.com/migrating-from-linq-to-sql-to-entity-framework-eager-loading/

  • Anonymous
    October 07, 2008
    When is the next release of Entity Framework scheduled?

  • Anonymous
    October 07, 2008
    Unfortunately, .Include supports loading only one related end, thus making us load each and every related end by code. I'd think that there should be something like .Include("") to load all referenced entities, and event .Include("/*") for getting all cascaded entities. If i'm writing a service that returns an entity with all related data, I won't want to start writing numerous lines of code, just to mention that "oh, yes, of course I want to return ALL the data of the entity".

  • Anonymous
    October 07, 2008
    Nice work. Looking forward to the rest of this series

  • Anonymous
    October 08, 2008
    Fantastic! I've always hated the way LINQ2SQL handled eager fetching. But, where is the strong typing. A string just doesn't seem like the ideal way of specifying this.

  • Anonymous
    October 08, 2008
    The comment has been removed

  • Anonymous
    October 08, 2008
    How do you eager load properties of a subtype? For example, let's say I have a type Department which has a navigation property (0..1) Chairman of type Employee.  Some of the employees are Managers.  Managers has a Supervises property which returns a list of Employees.  When I eager load the department Chairman, I would also like to eager load all Employees supervised if the Chairman is a Manager. In other words, something like this (I'm making up the syntax): .Include("Chairman").Include("(Chairman as Manager).Employees"). ?

  • Anonymous
    October 08, 2008
    The comment has been removed

  • Anonymous
    October 09, 2008
    I second the request by Craig Stuntz. Self joins with subtyped nodes are common and we don't want to recursively load these relationships. When I give the childrennav property name with include, why can't it resolve n levels deep for me? Thanks,  Travis

  • Anonymous
    October 09, 2008
    Weekly digest of interesting stuff

  • Anonymous
    October 13, 2008
    Since the release of the Entity Framework, Microsoft got a number of requests for details and best practices

  • Anonymous
    October 16, 2008
    Last week, we covered Eager Loading and how to migrate LINQ to SQL based code that took advantage of

  • Anonymous
    October 17, 2008
    Eager loading in EF apparently has limitations on SQL2000 - we weren't able to Include() nested levels, and had to use separate Load() calls after completing the initial query. In other words, we had to use multiple sequential queries. However, when we used SQL2005 as the underlying DB, with MARS enabled, the nested Include()'s worked fine. Does this mean EF requires MARS (Multiple Active Result Sets) to do nested Include()'s ? And by extension, does it therefore need MARS to fully support eager loading?

  • Anonymous
    October 17, 2008
    Let me modify my previous comment by specifying that the nested Include()'s that failed were not just nested, but were to the same underlying table. Several Navigation Properties of the entity being queried were actually associating to the same underlying table, and this seems to have been the source of the conflict.

  • Anonymous
    November 02, 2008
    I'm in agreement with Joseph and Mark's comments, if we can get an option for strongly-typed Include statements to use as an option instead of strings that would be great.  After using LINQ to get away from strings of SQL it is unfortunate to have to embed strings back in as part of Includes.  It just seems a little brittle.

  • Anonymous
    February 16, 2009
    A few things I have noticed in the Comments, some said they didn't like the use of a string in the "include" (no strong typing), and I also saw someone say it does not support multuple levels of associated entities. As far as strong typing the query as opposed to writing the table name in it, I haven't seen a clever way around that, the closest I can get is: Instead of this: Product firstProduct =        db.Product.Include("OrderDetail").Include("Supplier").First(); Write this: Product firstProduct =        db.Product.Include(db.OrderDetail.CommandText).Include(db.Supplier.CommentText).First(); And as far as supporting multiple levels, you can write something like this: Product firstProduct =        db.Product.Include(db.OrderDetail.CommandText + "." + db.OrderDetailLevel2.CommandText).Include(db.Supplier.CommentText + "." + db.SupplierLevel2.CommandText).First(); You see, the "Include" accepts a "dot-delimited" list of an existing path of associated entities Hope this helps somebody!

  • Anonymous
    February 21, 2009
    I wrote a blog post last year about a possible way to use Include with expressions rather than strings in Entity Framework: http://blogs.msdn.com/stuartleeks/archive/2008/08/27/improving-objectquery-t-include.aspx

  • Anonymous
    April 13, 2009
    Daniel, Instead of using the CommandText to supply a string representation of the object to be "Included", why not support an expression predicate as LINQ to SQL does in the LoadWith option. That provides a richer option as you can not only strongly type the child relationship, but can also add filtering as well. Thus you could have: Product firstProduct = db.Product.Include(p => p.OrderDetail); Or Product firstProduct = db.Product.Include(p => p.OrderDetail.OfType<ActiveOrders>()); Of course this would entail parsing more expression trees as part of the query evaluation, but could provide a much richer implementation.

  • Anonymous
    May 15, 2009
    Since Entity Framework doesn't even support enumerations, why would people want to migrate away?  I guess there are some advanced scenarios where a person would need to, but IMO, Entity Framework just isn't developed enough yet.  Agree?  Disagree?

  • Anonymous
    May 21, 2009
    I also saw someone say it does not support multuple levels of associated entities. As far as strong typing the query as opposed to writing the table name in it, I haven't seen a clever way around that, the closest I can get is: Instead of this: Product firstProduct =        db.Product.Include("OrderDetail").Include("Supplier").First(); Write this: Product firstProduct =        db.Product.Include(db.OrderDetail.CommandText).Include(db.Supplier.CommentText).First(); And as far as supporting multiple levels, you can write something like this: Product firstProduct =        db.Product.Include(db.OrderDetail.CommandText + "." + db.OrderDetailLevel2.CommandText).Include(db.Supplier.CommentText + "." + db.SupplierLevel2.CommandText).First(); You see, the "Include" accepts a "dot-delimited" list of an existing path of associated entities Hope this helps somebody!

  • Anonymous
    July 04, 2009
    This is fantastic. I am going to add it to my sites. Thanks!

  • Anonymous
    July 23, 2009
    Fantastic! I've always hated the way LINQ2SQL handled eager fetching. But, where is the strong typing. A string just doesn't seem like the ideal way of specifying this.

  • Anonymous
    July 23, 2009
    This is fantastic. I am going to add it to my sites. Thanks!

  • Anonymous
    January 10, 2010
    I would like to have a way to specify with "Include" more than one property in the same string. I could bring the possibily to have in one method the option to define wich relationships i'm interested to load.

  • Anonymous
    April 15, 2010
    Since Entity Framework doesn't even support enumerations, why would people want to migrate away?..

  • Anonymous
    June 10, 2010
    Hi, Thanks for information you have shared. My database has 'soft deleted' rows that should not be fetched (i.e., there is an 'isActive' column) I've tried my best to express this in the LINQ to Entities syntax but has been unable to. Can you help please? So, imagine I want to retrieve customers (primary entity) and related purchased-products (the 'include') where the purchased-product is mark as 'active' (i..e, we want to exclude the 'inactive'). Thanks!

  • Anonymous
    December 02, 2014
    This is a good article. I got the nice things from this. Thanks.