Linq

Eager Loading

by Sachin Singh

Posted on Saturday, 06 February 2021

Tags: Eager Loading in Entity Framework

We know lazy loading causes N+1 issue and unnecessary server trips which degrades the performance, so we can use eager loading , eager loading is the opposite of lazy loading so instead of loading related entities on demand , we load them upfront to prevent additional query requests to the database.

We use Incude() extension method to load related entities.


var result= db.Categories.Include("Posts").ToList();

Now the linq provider will translate the above query into corresponding T-Sql statements , which obviously will be a Join query on Category and Post , and send it to the database and thus a single request goes to the database and all categories with related Posts gets loaded into the memory, so when we iterate over the query result the items are fetched from the memory and no further request goes to the database.

In the above example we used the magic string to load the Post entity , this is a bad programming practice because later if we change the name of Post to something else like article then the code will break and EF will not be able to eager load because there will be no such Entity called Posts , but unfortunately we will not get any compile time error because it is wrap under double quotes , so always avoid magic string and use lambda expression so that if you change the entity name then you could get a compile time error immediately by visual studio intelligence. 


var res= db.Categories.Include(x=>x.Posts).ToList();

Load Multiple Entities

Sometimes we need to eagerly load entities upto multiple levels. For example, the following query eagerly loads the Category, Post,comment and User entities:

Example : fetch all categories with all posts , all comments on the post and their respective commentator name.


      public static void Main()
        {
            var db = new BlogDbContext();
            
            var categories = db.Categories.Include(x=>x.Posts.Select(c=>c.Comments.Select(u=>u.User))).ToList();
            foreach(var cat in categories)
            {
                Console.WriteLine("Category :{0} | Number of Posts :{1}", cat.CategoryName,cat.Posts.Count());
            }
            Console.ReadLine();
        }

Here we are moving from category to post and then Post to comments to user. As Posts is a collection property of category so we used select() method , but if we move on the opposite direction means from comment to post to category then we can simply include them as shown below.

Example : Display all Comments their commentator name and the post title of the related post and the category post belongs to.


      public static void Main()
        {
            var db = new BlogDbContext(); 
            var comments = db.Comments.Include(x => x.Post.Comments.Select(u=>u.User)).ToList();
            foreach(var comment in comments)
            {
                Console.WriteLine("Comment :{0} on Post{1} commented by:{3}",comment.CommentBody,comment.Post.Title,comment.User.Name);
            }
            Console.ReadLine();
        }

So select() method is needed to include collection property only, and for singleton navigation property we can include them by specifying the name of the object, followed by a dot (period) followed by the property name.


Like0
unlike0