Linq

Explicit loading

by Sachin Singh

Posted on Saturday, 06 February 2021

Tags: Explicit loading in Entity Framework

let's first discuss why do we even need explicit loading.

Problem with lazy loading : lazy loading causes n+1 issue means to load N entity and their related entities we end up with N+1 query requests to the database which is bad for the performance of a software. In lazy loading with each iteration, a new request goes to the database which is obviously not good.

Problem with eager loading : To avoid the n+1 issue we may use eager loading where we eagerly include the related entities along with the main entity such that the main entity along with the related entities gets loaded into the memory later when we fetch them from memory no further request goes to the database thus it resolves the N+1 issue by sending a single request goes to the database. But there is a drawback if we use multiple include() operators or the include operator contains multi-level related data then it leads to a complex giant query, and belief me, a giant complex query is worse than multiple query requests to the database.

So if you want to achieve the following
  1. Less number of request to the database
  2. Lighter query

Then you need to use Explicit loading, explicit loading as the name suggests loads the entity explicitly, so it is completely in our hand that how many requests we want to send to the database. To load an Entity we just need to use the Load() method at the end of the query.

You Don't need Collection(), Reference() or Entry()

Although there are several Operators like collection(), Entry(), Reference() that you will find in other blog posts or even in MSDN documents while searching Explicit loading but believe me they are of no use and you can simply achieve the same result with just the Load() method.

Example : display all comments on Post 1.


 var post=  db.Posts.Where(p=>p.PostId==1).Single();
 db.Entry(post).Collection(c=>c.Comments).Load(); //loads comment collection explicitly
 Console.WriteLine("PostTitle:{0}",Post.PostTitle);
 foreach(var comment in post.comments)
 {
 Console.WriteLine("{0},comment.CommentBody);
 }

Example: Display category name of Post 1.


 var post=  db.Posts.Where(p=>p.PostId==1).Single();
 db.Entry(post).Reference(c=>c.Category).Load(); //loads category

You can see in the above examples methods like Collection() , Entry() and Reference() have been used but the same could be achieved without using any of them as shown below.


  var post = db.Posts.Where(p => p.PostId == 1).Single();
  db.Posts.Where(p => p.PostId == post.PostId).Select(c=>c.Comments).Load();// load all comments with post

More over the methods like Collection() , Entry() and Reference() only work when we move either from single object to collection like Post 1 to all related comments or single object to single related entity like Post 1 to category entity but you can't use them to explicitly load collection of entities and their related entities neither you can filter them by related entity's property.

Example : display all the categories and related posts where post title is Post1.

This can't be achieved with the above technique , so instead remembering all those methods() just remember the load method and you can explicitly load any related entity as shown below.


 var categories= db.Categories.ToList(); // one request to the database
 db.Categories.Select(x=>x.Posts.Where(p=>p.Title=="Post1")).Load(); // load related posts.
 foreach (var cat in categories)
 {
    Console.WriteLine(cat.CategoryName);
    foreach (var post in cat.Posts)
    {
        Console.WriteLine(post.Body);
    }
  }

Like0
unlike0