EF代码优先改善自我参照的性能，一对多的关系 [英] EF Code First improving performance for self referencing, one to many relationships
问题描述
我有一个 AccountGroup 这是一个自引用的实体。叶 AccountGroup 可以包含1个或多个帐户。这两个实体拥有余额属性。每个 AccountGroup 有一个余额这是余额之和 S IN子组或（在叶组的情况下）余额一切都账户的总和。
为了建立所有的树列表中 AccountGroup 和帐户的I有递归遍历这个对象图，这导致了很多（我意味着很多！）调用到DB ...
中有没有办法在这样的方式来改善这一点，＃DB电话减少？
感谢
下面是下调代码
帐户（属于只有1 AccountGroup）
公共类帐户
{
公INT标识{搞定;组; }
公众诠释的GroupId {搞定;组; }
公共字符串名称{;组; }
公共小数平衡{搞定;组; }
公共字符串ACCOUNTTYPE {搞定;组; }
公共虚拟AccountGroup集团{搞定;组; }
}
AccountGroup（有0个或多个AccountGroups，有1个或多个账户，如果它是叶）
公共类AccountGroup
{
公共AccountGroup（）
{
账户=新的HashSet<&帐户GT;（）;
两组=新的HashSet< AccountGroup>（）;
}
公众诠释标识{搞定;组; }
公共BOOL IsRoot {{返回家长== NULL; }}
公共BOOL传递isLeaf {{返回Groups.Any（）！; }}
公共小数平衡{{返回传递isLeaf？ Accounts.Sum（A => a.Balance）：Groups.Sum（G => g.Balance）; }} //如果叶组，让所有账户余额之和，否则得到的所有子群
公众诠释的总和？ {的ParentId获得;组; }
公共字符串名称{;组; }
公共字符串描述{搞定;组; }
公共虚拟的ISet<&帐户GT;帐户{搞定;私人集; }
公共虚拟的ISet< AccountGroup>论坛{搞定;私人集; }
公共虚拟AccountGroup家长{搞定;组; }
}
调用代码
//开始处理根组（无母的）
的foreach（在db.AccountGroups.Include VAR rootGroup（G => g.Groups）。凡（G = > g.ParentId == NULL））
{
TraverseAccountGroup（rootGroup，0）;
}
//递归方法
私有静态无效TraverseAccountGroup（AccountGroup accountGroup，INT级）
{
//
//过程帐户组
//
Console.WriteLine（{0} {1}（{2}），String.Empty.PadRight（等级* 2，'。'），accountGroup.Name，水平）;
//
//如果存在子组，处理recursivelly
//
如果（accountGroup.Groups.Any（））
{
的foreach（VAR亚组accountGroup.Groups）
{
TraverseAccountGroup（亚组中，级别+ 1）;
}
}
//
//否则，属于叶亚群
//
，否则
{
进程帐户的foreach（在accountGroup.Accounts VAR帐户）
{
Console.WriteLine（帐户[{0}]，account.Name）;
}
}
}
CTE方法
有两种方式来增加对树的数据类型的查询速度。第一个（也是最简单的可能）是使用存储过程和执行EF的SQL功能加载树。存储过程的将缓存和结果集的执行速度会有所提高。我在存储过程中的查询建议是一个递归CTE。
的 http://msdn.microsoft.com/en-us/library/ms186243（v = SQL.105）的.aspx
以<&CTEName GT;为
（
选择
<根查询>
从<表>
UNION ALL
选择
<儿童查询>
从<表>
INNER JOIN< CTEName>
针对< CTEJoinCondition>
，其中
<终止条件>
）
修改
执行你的存储过程或CTE内嵌有：
的DbContext CTX =新SampleContext（）;
ctx.Database.SqlQuery< YourEntityType>（@SQL存储过程或命令HERE，新的[] {参数1，参数2，ETC}）;
拼合你的树结构
第二个方法是建立你的树的平面表示。可以弄平树成用于快速查询平面结构，然后使用平的结构和实际的树节点切出自参考实体之间的连接。您可以使用上述递归CTE查询构建扁平结构
这仅仅是一种方法，但也有很多关于这个主题的论文：
编辑：添加额外的澄清
刚一说明，递归CTE缓存对查询符号遍历结构之前。这是编写一个查询来解决问题最快和最简单的方法。然而，这却是一个SQL查询。您可以使用直接执行SQL或可以执行一个存储过程。存储过程缓存后被跑得那么他们的表现比必须建立之前运行的执行计划本机查询更好地执行图形。这完全取决于你。
与树的平面表示这个问题是你必须常规地重建或持续维持足够的平面结构。根据您的查询路径将决定你应该使用什么压扁算法，但最终的结果仍然是相同的。平面结构为完成你要EF里面做什么，而不必欺骗，并通过DBConnection的执行原始的SQL的唯一途径。
I have an AccountGroup which is a self-referencing entity. A leaf AccountGroup can contain 1 or more Accounts. Both entities have Balance property. Each AccountGroup has a Balance which is either a sum of Balances in sub-groups or sum of Balances of all Accounts (in case of leaf group).
In order to build a tree listing of all AccountGroups and Accounts I have to traverse this object graph recursively, which causes a lot (I mean a lot!!!) of calls to DB...
Is there any way to improve upon this in such way that # of DB calls is reduced?
Thanks
Here is the trimmed down code
Account (belongs to only 1 AccountGroup)
public class Account
{
public int Id { get; set; }
public int GroupId { get; set; }
public string Name { get; set; }
public decimal Balance { get; set; }
public string AccountType { get; set; }
public virtual AccountGroup Group { get; set; }
}
AccountGroup (has 0 or many AccountGroups, has 1 or more Accounts if it is a leaf)
public class AccountGroup
{
public AccountGroup()
{
Accounts = new HashSet<Account>();
Groups = new HashSet<AccountGroup>();
}
public int Id { get; set; }
public bool IsRoot { get { return Parent == null; } }
public bool IsLeaf { get { return !Groups.Any(); } }
public decimal Balance { get { return IsLeaf ? Accounts.Sum(a => a.Balance) : Groups.Sum(g => g.Balance); } } // if leaf group, get sum of all account balances, otherwise get sum of all subgroups
public int? ParentId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public virtual ISet<Account> Accounts { get; private set; }
public virtual ISet<AccountGroup> Groups { get; private set; }
public virtual AccountGroup Parent { get; set; }
}
Calling Code
// start processing root groups (ones without parent)
foreach (var rootGroup in db.AccountGroups.Include(g=>g.Groups).Where(g => g.ParentId == null))
{
TraverseAccountGroup(rootGroup, 0);
}
// recursive method
private static void TraverseAccountGroup(AccountGroup accountGroup, int level)
{
//
// process account group
//
Console.WriteLine("{0}{1} ({2})", String.Empty.PadRight(level * 2, '.'), accountGroup.Name, level);
//
// if subgroups exist, process recursivelly
//
if (accountGroup.Groups.Any())
{
foreach (var subGroup in accountGroup.Groups)
{
TraverseAccountGroup(subGroup, level + 1);
}
}
//
// otherwise, process accounts belonging to leaf subgroup
//
else
{
foreach (var account in accountGroup.Accounts)
{
Console.WriteLine("ACCOUNT [{0}]", account.Name);
}
}
}
CTE Approach
There are two ways to increase speed of queries against tree data types. The first (and likely easiest) is using a Stored Procedure and the execute sql functionality of EF to load the tree. The SProc will cache and the result set execution speed will be increased. My recommendation for the query in the sproc would be a recursive CTE.
http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
with <CTEName> as
(
SELECT
<Root Query>
FROM <TABLE>
UNION ALL
SELECT
<Child Query>
FROM <TABLE>
INNER JOIN <CTEName>
ON <CTEJoinCondition>
WHERE
<TERMINATION CONDITION>
)
Edit
Execute your sproc or CTE inline with:
DbContext ctx = new SampleContext();
ctx.Database.SqlQuery<YourEntityType>(@"SQL OR SPROC COMMAND HERE", new[] { "Param1", "Param2", "Etc" });
Flatten Your Tree Structure
The second approach is to build a flat representation of your tree. You can flatten a tree into a flat structure for quick querying and then use a linkage between the flat structure and the actual tree node to cut out the self referencing entity. You can build the flat structure using the above recursive CTE query.
This is just one approach but there are many papers on the subject:
EDIT: Adding additional clarification Just a note, the Recursive CTE cache's the symbols for the query before iterating over the structure. This is the fastest and simplest way to write a query to solve your problem. However, this HAS to be a SQL query. You can use execute sql directly or you can execute a SProc. Sprocs cache the execution graph after being ran so they perform better than native queries that have to build an execution plan prior to running. This is entirely up to you.
The issue with a flat representation of your tree is you have to routinely rebuild or constantly upkeep the flat structure. Depending on your query path would determine what flattening algorithm you should use, but the end result remains the same. The flat structure is the only way to "accomplish" what you want to do inside EF without having to cheat and execute raw SQL through the DBConnection.
这篇关于EF代码优先改善自我参照的性能，一对多的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
