实体框架生成不必要的查询
假设我有三个表： 类别、项目和CategoriesToItems
类别和项目之间的关系是多对多的（通过CategoriesToItems 表）。
假设我调用某个方法，并在该方法内部执行以下代码（以立即强制执行查询/枚举数据）：
var categories = context.Categories.ToList();
var items = context.Items.ToList();
var links = context.CategoriesToItems.ToList();
紧接着，我开始像这样检查每个产品：
foreach(Category category in categories)
{
foreach(Item item in links.Select(l => l.Item))
{
//Do some stuff.
}
}
为什么这些 foreach 循环会生成以下查询：数据库？因为我加载了类别、项目和链接表条目，所以 EF 不应该已经知道所有必需的信息吗？
有趣的是，如果我稍微改变一下并加载数据，
var items = context.Items.ToList();
var categories = context.Categories.Include("CategoriesToItems").ToList();
当我想知道任何给定类别中的产品时，运行类似的循环不会生成查询。
我关心的原因是，第一种方式加载数据要快得多，但我必须随后立即对数据执行操作，并且性能要慢得多（因为每次传递都会生成额外的查询）。
实际的 include 子句更复杂，因为我们需要来自多个不同表的数据，这就是为什么单独加载整个表比通过精心生成的多重连接查询加载整个表更快（我们需要其中的所有数据）。在实际的应用程序中，涉及更多的表（类别可以有标签，项目可以有标签，这两种关系都是多对多——我相信你明白了——“做东西”部分实际上是在构建一个项目/类别的字典到它们所拥有的标签，并且标签是继承的，因此标有 X 的类别意味着其所有产品都标有 X）。
稍微概括/简化一下： 当我从所有涉及的表中加载每一行时，为什么实体框架会生成有关关系的查询？我能做些什么来帮助这种情况吗？
编辑：我应该补充一点，我假设它实际上正在调用数据库，因为当这些特定代码行执行时，LiveTrace 中会显示 ADO.NET 查询条目，并且性能是明显不同。因此，如果我误解了那些 LiveTrace 条目的含义，那么我想我遇到了一个不同的问题。
Let's suppose I have three tables:
Categories, Items, And CategoriesToItems
The relationship between Categories and Items is many-to-many (through the CategoriesToItems table).
Let's suppose that I call some method and inside of that method we execute the following code (to force query execution/enumeration of the data immediately):
var categories = context.Categories.ToList();
var items = context.Items.ToList();
var links = context.CategoriesToItems.ToList();
Immediately after this, I start examining each product like so:
foreach(Category category in categories)
{
foreach(Item item in links.Select(l => l.Item))
{
//Do some stuff.
}
}
Why do these foreach loops generate queries to the database? Shouldn't EF already know all of the required information because I loaded the categories, items, and the link table entries?
Interestingly, if I change things up a bit and load the data like so
var items = context.Items.ToList();
var categories = context.Categories.Include("CategoriesToItems").ToList();
Running through a similar loop doesn't generate queries when I want to know what products are in any given category.
The reason I care is that loading the data the first way is much faster, but I have to perform operations on the data immediately afterwards and the performance of that is much slower (because of the additional queries generated with each pass).
The actual include clauses are more complicated, as we need data from several different tables which is why it's faster to load the whole tables individually than to load the whole tables through an elaborately generated multiple join query (we need all of the data that's in them). In the real application there are more tables involved (categories can have tags, items can have tags, both relationships are many-to-many--I'm sure you get the picture--the "do stuff" section is actually building a dictionary of items/categories to the tags they have and tags are inherited, so a category tagged with X means all of its products are tagged with X).
To generalize/simplify this a little bit:
Why does the Entity Framework generate queries about relationships when I've loaded every single row from all of the involved tables? Is there anything I can do to help this situation?
Edit: I should add that I'm assuming it's actually making a call to the database because of the ADO.NET query entries that show up in LiveTrace when those specific lines of code execute and because the performance is noticeably different. So if I'm misunderstanding what those LiveTrace entries mean, then I guess I've got a different problem all together.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
尝试在执行任何查询之前关闭延迟加载：
Try to turn off lazy loading prior to executing any query: