We’ll learn how to handle data in EF Core using eager loading and query projection. The time has come for us to interact with related data; we’ve interacted with simple objects so far. Entity Framework Core always has an interesting way of handling related data.
Four methods are available for querying related data in EF Core.
- Eager Loading
- Query Projection
- Explicit loading
- Lazy Loading
As a follow-up to my previous blog, here is today’s post.
Inserting Related Data:
Let’s see how the related data is inserted in EFCore before moving on to Eager loading. The User and Rating entities have a one-to-many relation, which we implemented in part III. The rating-related data is now being inserted into the Users entity.
public static void AddUserWithRating()
{
var user = new User
{
Name = "Saravanan",
Rating = new Rating {RatingLevel="A"}
};
context.Users.Add(user);
context.SaveChanges();// Saves all changes made in this context to the database
}
User entity’s Rating data has been added using the above code.
public static void AddRatingtoExistingUsers()
{
var user = context.Users.ToList();
context.Ratings.Add(new Rating
{
RatingLevel = "B",
Users = user
});
context.SaveChanges();
}
All the Users in the context will have the Rating object added and updated using the method above.
This image clearly illustrates a batch update query and an insert query in the Rating table to insert a new rating record and update the users’ ratings.
Eager Loading:
This method enables retrieving related data in the same DB call as the DbSet Method Include().
public static void EargerLoadingWithRating()
{
var ratings = context.Ratings.Include(r => r.Users).ToList();
}
Data related to Users will be retrieved from the Ratings by the Include() method in the code.
Using Left Join Query, the data is retrieved in one query with rich objects.
var rating = context.Ratings.Include(r => r.Users).FirstOrDefault(u=>u.RatingLevel=="B");
There are also execution methods available in addition to FirstOrDefault(); however, as you can see below, these execution methods should always be called after the member that represents a database set.
Query Projection:
In SQL queries, it allows you to retrieve related data using the select() method.
public static void ProjectionLoadingWithRating()
{
var ratings = context.Ratings.Select(s => new { s.RatingLevel, s.Users }).ToList();
}
As you can see in the example above, the result is projecting an anonymous type, which can only be determined by its definition method.
Entities recognized by the DBContext model can only be tracked by EF Core.
Note:
1. Types that are anonymous cannot be tracked
2The properties of anonymous types are tracked.
Consider the code below.
public static void ProjectionLoadingWithRating()
{
var ratings = context.Ratings.Select(r => new { ratings=r , B_rating= r.Users.Where(q=>q.Name.Contains("gow"))}).ToList();
var rating = ratings[1].ratings.RatingLevel += "*";
}
The code above shows how to retrieve objects with ratings and B_rating using a local variable rating. Therefore, Rating entities have an anonymous rating level because we modified RatingLevel from the ratings.
A context should be used to track entities that belong to anonymous types.
The context tracks the RatingLevel property of the Rating entity which is an anonymous type that is modified. An anonymous property named RatingLevel is present in rating, as shown in the above image.
Conclusion:
- As we have seen, we have been able to insert the related data.
- Using the DbSet member Include(), we can retrieve the related data using Eager loading
- Select() returns anonymous results when using Query Projection to retrieve related data.