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

Question

I am using the following LINQ query for my profile page:

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 has a long graphic object and uses many inclusions. It works perfect now, but when a site has a lot of users, it will greatly affect performance?

Should I do it another way?

Answer 1

A query with inclusion returns a single result set, and the number of inclusions affects how a large set of data is transferred from the database server to the web server. Example:

Suppose we have an entity Customer (Id, Name, Address) and an entity Order (Id, CustomerId, Date) . Now we want to request a client with her orders:

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

The resulting dataset will have the following structure:

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

This means that Cutomers data is repeated for each Order . Now let's expand the example with other objects - "OrderLine" (Id, OrderId, ProductId, Quantity) and Product (Id, Name) `. Now we want to request a client with her orders, orders and products:

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

The resulting dataset will have the following structure:

  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, the data becomes pretty much duplicated. The total number of each of them refers to the correct navigation ( Product in the example) will add new columns and each of them will include collections in the navigation property ( Orders and OrderLines in the example) will add new columns and duplicate the already created rows for each row in the included collection.

This means that your example can easily contain hundreds of columns and thousands of rows that require a lot of data to transfer. The right approach is to create performance tests, and if the result does not meet your expectations, you can change your query and load the navigation properties separately with your own queries or the LoadProperty method.

Example of individual 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(); 

LoadProperty example:

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

Also, you should always download only the data that you really need.

Edit: I just created an offer

Answer 2

You can improve the performance of many of them by creating two or more small data queries from a database, as shown below.

In my experience, only can give a maximum of 2 includes per request , as shown below. More than this will give very poor 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(); 

Will output a small data set from the database using more moves to the database.

I wrote a blog post above this using my own experience. Here

Hope this helps you.

Answer 3

Yes it will. Avoid using Include if it extends multiple rows of data in a row in the master table.

I believe that EF converts a request into one large union instead of multiple requests. Thus, you will duplicate the main table data for each row of the detail table.

For example: Wizard → Details. Say a master has 100 lines, parts have 5,000 lines (50 for each master).

If you lazy-load the details, you return 100 lines (size: master) + 5000 lines (size: details).

If you use .Include ("Details"), you return 5,000 rows (size: master + details). Essentially, the bulk is duplicated more than 50 times.

It is multiplied up if you include multiple tables.

Check the SQL generated by EF.

Answer 4

I would recommend that you perform stress tests and measure site performance under stress. If you are performing complex queries for each query, you might consider caching some results.

Answer 5

The result of include may change: it depends on the object that invokes the include method.

Like the example proposed by Ladislav Mrnka, suppose we have an entity

Customer (identifier, name, address)

which are displayed in this table:

 Id | Name | Address ----------------------- C1 | Paul | XYZ 

and entity Order (Id, CustomerId, Total)

which are displayed in this table:

 Id | CustomerId | Total ----------------------- O1 | C1 | 10.00 O2 | C1 | 13.00 

The ratio of one client to many orders

---

Escal 1: Customer => Orders

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

Linq will be translated into a very complex sql query.

In this case, the request will generate two records, and the client information will be replicated.

  Customer.Id | Customer.Name | Order.Id | Order.Total ----------------------------------------------------------- C1 | Paul | O1 | 10.00 C1 | Paul | O2 | 13.00 

---

Escal 2: Order => Customer

 var order = context.Orders .Include("Customers") .SingleOrDefault(c => c.Id == "O1"); 

Linq will be converted to a simple sql join.

In this case, the request will produce only one record without duplication of information:

  Order.Id | Order.Total | Customer.Id | Customer.Name ----------------------------------------------------------- O1 | 10.00 | C1 | Paul