可以在EntityFramework中的ObjectSet中使用多少个包含来保留性能？ [英] How many Include I can use on ObjectSet in EntityFramework to retain performance?
问题描述
我正在为我的个人资料页面使用以下LINQ查询：
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）
其中u.UserId == userId
select u;
它有一个很长的对象图，并且使用了很多包括。它现在运行完美，但是当网站有很多用户时，会影响性能吗？
我应该以其他方式吗？
具有包含的查询返回单个结果集，并且包含的数量影响数据库从数据库服务器传输到Web服务器的大小。例如：
假设我们有一个实体 Customer（Id，Name，Address）和一个实体订单（Id，CustomerId，Date）。现在我们要查询客户的订单：
var customer = context.Customers
.Include（Orders ）
.SingleOrDefault（c => c.Id == 1）;
生成的数据集将具有以下结构：
Id |名称|地址| OrderId | CustomerId |日期
--------------------------------------------- ------
1 | A | XYZ | 1 | 1 | 1.1。
1 | A | XYZ | 2 | 1 | 2.1。
这意味着 Cutomers 数据被重复每个订单。现在我们可以使用另一个实体 - OrderLine（Id，OrderId，ProductId，Quantity）和 Product（Id，Name）扩展示例。现在我们要查询客户的订单，订单行和产品：
var customer = context.Customers
.Include（Orders.OrderLines.Product）
.SingleOrDefault（c => c.Id == 1）;
生成的数据集将具有以下结构：
Id |名称|地址| OrderId | CustomerId |日期| OrderLineId | LOrderId | LProductId |数量| 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 在示例中）将添加新列，并重复已包含的集合中每行的已创建行。
这意味着你的例子可以轻松地拥有数百列和数千行，这是很多数据要传输的。正确的方法是创建性能测试，如果结果不能满足您的期望，您可以通过自己的查询或 LoadProperty 方法单独修改查询并加载导航属性。 / p>
单独查询的示例：
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）;
此外，您应该总是只加载真正需要的数据。
编辑：我刚刚创建了 Data DataVoice 的提案，以支持其他热切加载策略，其中加载的数据将在附加结果集中传递（由同一数据库往返中的单独查询创建）。如果你发现这个改进很有趣，不要忘了投票支持该提案。
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 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?
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:
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.
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
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.
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.
Example of separate 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();
Example of 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.
Edit: 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中使用多少个包含来保留性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
