EF Code Performance Improvement for Self-Binding, One to Many Relationships

I have AccountGroup, which is a self-regulatory entity. The sheet AccountGroupmay contain 1 or more Accounts. Both objects have a property Balance. Each AccountGrouphas Balance, which is either the sum of Balancethe subgroups or the sum of Balanceall accounts (in the case of a group of leaves).

To build a list of trees of all AccountGroupand Account, I have to recursively move this object graph, which causes a lot (I mean a lot !!!) of calls to DB ...

Is there a way to improve this in such a way as to reduce the number of database calls?

thank

Here is the adjusted code

Account (belongs to only one account group)

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 ( 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);
        }
    }
}
+5
1

CTE

. (, , ) SQL- EF . SProc , . sproc 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

sproc CTE

DbContext ctx = new SampleContext();
ctx.Database.SqlQuery<YourEntityType>(@"SQL OR SPROC COMMAND HERE", new[] { "Param1", "Param2", "Etc" });

- . , node, . , CTE.

, :

http://www.governor.co.uk/news-plus-views/2010/5/17/depth-first-tree-flattening-with-the-yield-keyword-in-c-sharp/

EDIT: , CTE . . SQL-. execute sql , SProc. Sprocs , , , . .

, . , , . - "" , EF, SQL DBConnection.

0

All Articles