我可以在 EntityFramework 中的 ObjectSet 上使用多少个 Include 来保持性能? [英] How many Include I can use on ObjectSet in EntityFramework to retain performance?

问题描述

我对我的个人资料页面使用以下 LINQ 查询:

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;

它有一个很长的对象图并且使用了很多 Include.现在运行完美，但是当网站有很多用户时，会不会对性能有很大影响?

It has a long object graph and uses many Includes. It is running perfect right now, but when the site has many users, will it impact performance much?

我应该以其他方式做吗?

Should I do it in some other way?

推荐答案

包含包含的查询返回单个结果集，包含的数量会影响大数据集从数据库服务器传输到 Web 服务器的方式.示例:

A query with includes returns a single result set and the number of includes affect how big data set is transfered from the database server to the web server. Example:

假设我们有一个实体 Customer (Id, Name, Address) 和一个实体 Order (Id, CustomerId, Date).现在我们要查询一个客户的订单:

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

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

生成的数据集将具有以下结构:

The resulting data set 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.

这意味着对于每个 Order 重复 Cutomers 数据.现在让我们使用另一个实体扩展示例 - 'OrderLine (Id, OrderId, ProductId, Quantity)andProduct (Id, Name)`.现在我们要查询客户的订单、订单行和产品:

It means that Cutomers data are repeated for each Order. Now lets extend the example with another entities - 'OrderLine (Id, OrderId, ProductId, Quantity)andProduct (Id, Name)`. Now we want to query a customer with her orders, order lines and products:

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

生成的数据集将具有以下结构:

The resulting data set 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

如您所见，数据变得相当多.通常，每个包含到参考导航属性(在示例中为 Product)都会添加新列，并且每个包含到集合导航属性(Orders 和 OrderLines> 在示例中)将为包含的集合中的每一行添加新列并复制已创建的行.

As you can see data become quite a lot duplicated. Generaly each include to a reference navigation propery (Product in the example) will add new columns and each include to a collection navigation property (Orders and OrderLines in the example) will add new columns and duplicate already created rows for each row in the included collection.

这意味着您的示例可以轻松地拥有数百列和数千行，这些数据需要传输大量数据.正确的方法是创建性能测试，如果结果不能满足您的期望，您可以通过自己的查询或通过 LoadProperty 方法分别修改查询和加载导航属性.

It means that your example can easily have hundreds of columns and thousands of rows which is a lot of data to transfer. The correct approach is creating performance tests and if the result will not satisfy your expectations, you can modify your query and load navigation properties separately by their own queries or by LoadProperty method.

单独查询的示例:

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 示例:

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

此外，您应该始终只加载您真正需要的数据.

Also you should always load only data you really need.

我刚刚创建了 关于 Data UserVoice 的提案 支持额外的急切加载策略，其中急切加载的数据将在附加结果集中传递(由同一数据库往返中的单独查询创建).如果您觉得这项改进很有趣，请不要忘记投票支持该提案.

I just created proposal on Data UserVoice to support additional eager loading strategy where eager loaded data would be passed in additional result set (created by separate query within the same database roundtrip). If you find this improvement interesting don't forget to vote for the proposal.

这篇关于我可以在 EntityFramework 中的 ObjectSet 上使用多少个 Include 来保持性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！