我有一个自引用实体“AccountGroup”。叶子“AccountGroup”可以包含1个或多个“Accounts”。两个实体都有“Balance”属性。每个“AccountGroup”都有一个“Balance”,它要么是子组中“Balance”的总和,要么是所有帐户的“Balance”的总和(在叶子组的情况下)。
为了构建所有“AccountGroup”和“Account”的树形列表,我必须递归遍历此对象图,这会导致大量(我的意思是很多!)对数据库的调用...
有没有办法改进这种方式,以减少数据库调用次数?
谢谢
以下是精简的代码
为了构建所有“AccountGroup”和“Account”的树形列表,我必须递归遍历此对象图,这会导致大量(我的意思是很多!)对数据库的调用...
有没有办法改进这种方式,以减少数据库调用次数?
谢谢
以下是精简的代码
帐户(仅属于1个帐户组)
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; }
}
账户组(如果是叶子节点,则有0个或多个账户组,有1个或多个账户)
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; }
}
调用代码
// 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);
}
}
}
SqlQuery
仍然是一个有效的选项。 - Ladislav Mrnka