如何优化我的EF Code First查询？ [英] How can I optimize my EF Code First query?

问题描述

[已更新 - 请参阅底部的更新

]

我正在使用EF代码，我一般都很高兴。然而，一个简单（和常见的）操作是导致EF生成可疑复杂的SQL，这使我的应用程序下降。

我只是使用一个列表（整数）ID，但是因为我需要很多子实体的详细信息，所以我使用 .Include（）来获取加载在同一个子实体上的子实体时间如下：

  db.MyEntities 
 .Where（x => x.ClientId == clientId） 
 .Where（x => ids.Contains（x.Id））
 .Where（x => x.SubEntity1！= null）
 .Include（x => x .SubEntity1）
 .Include（x => x.SubEntity1.SubSubEntity1）
 .Include（x => x.SubEntity1.SubSubEntity2）
 .Include（x => x。 SubEntity1.SubSubEntity3）
 .Include（x => x.SubEntity1.SubSubEntity4）
 .Include（x => x.SubEntity2）
 .Include（x => x.SubEntity2 .SubSubEntity1）
 .Include（x => x.SubEntity2.SubSubEntity2）
 .Include（x => x.SubEntity2.SubSub Entity3）
 .Include（x => x.SubEntity2.SubSubEntity4）
 .Include（x => x.SubEntity3）
  

正如你所看到的，它不是一个特别复杂的查询，除了所有这些 Include s。

EF为此生成的SQL 巨大 - 围绕SQL的 74Kb 。执行不需要很长时间（因为通常，ID列表中的项目数量很少），而是仅仅构建查询之前的EF ，即在查询之前甚至被发送到数据库。

如果我删除 Includes ，那么查询要小得多，整个事情需要更少的时间 - 但是各种相关实体然后一个一个地被加载，这不能很好地扩展。

EF似乎给了我加载数据的两个选项：

1. 在初始查询期间一次加载所有子实体（使用 Include

2. 一次加载子实体（使用延迟加载，或显式使用加载 / LoadProperty ）。



选项1将作为我喜欢的选择，如果它的工作，但由于这不工作在这种情况下，我唯一剩下的选项是2 - 我不认为这是可以接受的：有数据库查询过多，其中输入的ID列表（即实体数量）很大。

在我看来，EF似乎没有解决的另一个选项：获取主要实体，获取所有相关的SubEntity1实体，然后是所有相关的SubEntity2实体等。这样，查询的数量与要获取的实体的数量类型相关，而与实体的数量有关。这可能会更好一些。

我在EF中看不到一种方法：换句话说，说为所有这些实体加载这个属性（在单个查询）。

我只需要放弃EF并编写自己的SQL？

---

更新
我注意到，即使我删除了 Include s，生成的SQL比我认为应该是这样的，我认为这一切都来自于EF并不喜欢我的桌面结构。我挣扎了几天让EF创建了我通过Code First（和Fluent API）寻找的数据库结构，即使我已经到了（几乎）我想要的地方，我不得不接受一些妥协。 / p>

我想我现在要进一步处罚，因为敢于做某些EF不希望我做的事情。看起来像一个简单的查询比它应该更复杂，而且稍微复杂一点的查询大大地更复杂。

这是令人难以置信的令人沮丧的 - 我以为我把所有的这些EF麻烦都放在后面，系统现在正在生产中有数十个用户 - 这将使它非常困难让我重新开始。

似乎我将永远花费永远的EF牙齿和指甲。我希望我从来没有使用过它！

无论如何，回到我原来的问题：如果我有一堆类型为A的实体，我想在一个查询中加载类型为B 的相关子实体，有没有办法？

解决方案

如何使用存储过程加载数据？是的，这有点脏，但是当我遇到EF的性能问题时，这是我做的。我希望我没有在你的问题上遗漏一些东西。

http://msdn.microsoft.com/en-US/data/jj691402

[Updated - see update at bottom]

I am using EF code-first, and am generally happy with it. However, one simple (and common) operation is causing EF to generate ridiculously complex SQL, which is slowing my application down.

I am simply fetching a set of entities using a list of (integer) IDs, but because I need details of lots of sub-entities, I'm using .Include() to get those sub-entities loaded at the same time, as follows:

db.MyEntities
    .Where(x => x.ClientId == clientId)
    .Where(x => ids.Contains(x.Id))
    .Where(x => x.SubEntity1 != null)
    .Include(x => x.SubEntity1)
    .Include(x => x.SubEntity1.SubSubEntity1)
    .Include(x => x.SubEntity1.SubSubEntity2)
    .Include(x => x.SubEntity1.SubSubEntity3)
    .Include(x => x.SubEntity1.SubSubEntity4)
    .Include(x => x.SubEntity2)
    .Include(x => x.SubEntity2.SubSubEntity1)
    .Include(x => x.SubEntity2.SubSubEntity2)
    .Include(x => x.SubEntity2.SubSubEntity3)
    .Include(x => x.SubEntity2.SubSubEntity4)
    .Include(x => x.SubEntity3)

As you can see, it's not a particularly complex query, with the exception of all those Includes.

The SQL that EF generates for this is huge - around 74Kb of SQL. It doesn't take very long to execute (since normally the number of items in the list of IDs is small), but it takes EF more than a second just to construct the query - i.e. before the query is even sent to the database.

If I remove the Includes, then the query is much smaller, and the whole thing takes much less time - but the various related entities are then loaded one-at-a-time, which doesn't scale well.

EF seems to give me two options for loading the data:

1. Load all the sub-entities at once during the initial query (using Include as above), or
2. Load the sub-entities one-at-a-time (using lazy loading, or explicitly using Load/LoadProperty).

Option 1 would be my preferred option if it worked, but since that doesn't work in this case, my only remaining option is 2 - and I don't think that's acceptable: there would be too many database queries where the input list of IDs (i.e. the number of entities) is large.

It seems to me that there is another option that EF doesn't seem to address: having fetched the main entities, fetch all the relevant SubEntity1 entities, then all the relevant SubEntity2 entities, etc. That way, the number of queries is related to the number of types of entity to be fetched, rather the number of entities. This would scale much better.

I can't see a way to do that in EF: in other words, to say "load this property for all these entities (in a single query)".

Will I just have to give up on EF and write my own SQL?

---

UPDATE I have noticed that even if I remove the Includes, the SQL generated is more complex than I think it should be, and I think this all stems from the fact that EF does not 'like' my table structure. I struggled for days to get EF to create the database structure I was looking for via Code First (and the Fluent API), and even when I had got to (nearly) where I wanted to be, I had to accept some compromises.

I think I'm now paying a further penalty for daring to do something that EF didn't want me to do. It looks like a simple query is more complex than it should be, and a slightly-more-complex query is massively more complex.

This is incredibly depressing - I thought I'd left all those EF hassles behind, and the system is now in production with dozens of users - which would make it very difficult for me to start over.

It seems I'm going to have to spend the eternity fighting EF tooth and nail at every turn. How I wish I'd never used it in the first place!

Anyway, back to my original question: if I have a bunch of entities of type A for which I want to load the related sub-entities of type B in one query, is there a way to do that?

解决方案

How about loading the data using stored procedures? Yes, it is a bit dirty, but this is what I do when I hit performance issues with EF. I hope I'm not missing something in your question.

http://msdn.microsoft.com/en-US/data/jj691402

这篇关于如何优化我的EF Code First查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！