How much Include in EntityFramework can I use on an ObjectSet to maintain performance?

asp.net-mvc-2 entity-framework linq linq-to-entities performance

Question

For my profile page, I'm using the following LINQ query:

var userData = from u in db.Users
                        .Include("UserSkills.Skill")
                        .Include("UserIdeas.IdeaThings")
                        .Include("UserInterests.Interest")
                        .Include("UserMessengers.Messenger")
                        .Include("UserFriends.User.UserSkills.Skill")
                        .Include("UserFriends1.User1.UserSkills.Skill")
                        .Include("UserFriends.User.UserIdeas")
                        .Include("UserFriends1.User1.UserIdeas")
                               where u.UserId == userId
                               select u;

It employs several Includes and has a lengthy object graph. The site is now operating well, but will increased user traffic have a significant influence on performance?

Should I carry it out differently?

1
47
12/6/2015 6:19:57 AM

Accepted Answer

The number of inclusions in a query determines how much data is sent from the database server to the web server when a single result set is returned. Example:

Let's say we have a thing.Customer (Id, Name, Address) and a thingOrder (Id, CustomerId, Date) . We now wish to inquire about a customer's orders:

var customer = context.Customers
                      .Include("Orders")
                      .SingleOrDefault(c => c.Id == 1);

The final data set will be organized as follows:

 Id | Name | Address | OrderId | CustomerId | Date 
---------------------------------------------------
  1 |  A   |   XYZ   |    1    |     1      | 1.1.
  1 |  A   |   XYZ   |    2    |     1      | 2.1.

It implies that.Cutomers Information is repeated for eachOrder . Let's add more entities to the sample now, such as "OrderLine" (Id, OrderId, ProductId, Quantity)and Item (Id, Name)'. Now, let's inquire about a customer's orders, order lines, and products:

var customer = context.Customers
                      .Include("Orders.OrderLines.Product")
                      .SingleOrDefault(c => c.Id == 1);

The final data set will be organized as follows:

 Id | Name | Address | OrderId | CustomerId | Date | OrderLineId | LOrderId | LProductId | Quantity | ProductId | ProductName
------------------------------------------------------------------------------------------------------------------------------
  1 |  A   |   XYZ   |    1    |     1      | 1.1. |     1       |    1     |     1      |    5     |    1      |     AA
  1 |  A   |   XYZ   |    1    |     1      | 1.1. |     2       |    1     |     2      |    2     |    2      |     BB
  1 |  A   |   XYZ   |    2    |     1      | 2.1. |     3       |    2     |     1      |    4     |    1      |     AA
  1 |  A   |   XYZ   |    2    |     1      | 2.1. |     4       |    2     |     3      |    6     |    3      |     CC

As you can see, data started to replicate quite a bit. Typically, each has a reference navigation feature (Product in the illustration) will include each new column in a collection navigation property.Orders and OrderLines (In the example) will duplicate existing rows and add new columns for each row in the provided collection.

You could potentially have thousands of rows and hundreds of columns in your example, which means there would be a lot of data to upload. Making speed tests is the right course of action, and if the results don't meet your expectations, you may change your query and load navigation settings individually by using separate queries or byLoadProperty method.

Examples of distinct queries

var customer = context.Customers
                      .Include("Orders")
                      .SingleOrDefault(c => c.Id == 1);
var orderLines = context.OrderLines
                        .Include("Product")
                        .Where(l => l.Order.Customer.Id == 1)
                        .ToList();

a case ofLoadProperty :

var customer = context.Customers
                      .SingleOrDefault(c => c.Id == 1);
context.LoadProperty(customer, c => c.Orders);

Additionally, you should only ever load the data you really need.

Edit: To provide an extra eager loading technique where eager loaded data would be sent in an additional result set, I recently designed the a suggestion for Data UserVoice (created by separate query within the same database roundtrip). Do not forget to cast your vote in favor of the improvement if you think it intriguing.

84
5/8/2015 10:21:33 AM

Popular Answer

You may enhance numerous functions, like two or more tiny data requests are made, from the data base as shown below.

Only can provide two contains most per query as below, in my experience. Any more will result in very subpar performance.

var userData = from u in db.Users
                        .Include("UserSkills.Skill")
                        .Include("UserIdeas.IdeaThings")
                        .FirstOrDefault();

 userData = from u in db.Users
                    .Include("UserFriends.User.UserSkills.Skill")
                    .Include("UserFriends1.User1.UserSkills.Skill")
                    .FirstOrDefault();

By making further trips to the database, the aforementioned will bring a tiny data collection from it.

This blog article was written by me using my personal experience. Time is Here.

I hope that was helpful.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow