CODEWITHSUNDEEP
c#sqllinqentity-frameworkrecursion
Florian
Florian

Reputation: 5994

EF recursive hierarchy

I've got some kind of layers in my application. These layers are following a structure like: a company has settlements, settlements have sections, sections have machines, machines are producing items, to produce items the machine needs tools,... At the very end of this hierarchy there are entries how many items could be produced with a specific part of a tool(called cuttingtool). Based on that, a statistic can be calculated. On each layer the statistic results of the next upper layer are getting added. Take a look at this diagram: enter image description here

On each layer, a statistic is displayed. For example: The user navigates to the second layer(Items). There are 10 items. The user can see a pie chart which displays the costs of each item. These costs are calculated by adding all costs of the items tools(the next upper layer). The costs of the tools are calculated by adding all costs of the "parts of the tools"... I know that is a bit complicated so if there any questions, just ask me for a more detailed explaination.

Now my problem: To calculate the cost of an item(the same statistic is provided for machines, tools,... => for each layer on the diagram), I need to get all Lifetimes of the Item. So I am using a recursive call to skip all layers between the Item and the Lifetime. That workes quite well BUT I am using far to many SelectMany-linq commands. As a result, the performance is extremely bad. I've thought about a joins or procedures(stored in the database) to speed that up, but I am by far not experied which techniques like databases. So I want to ask you, what you would do? Currently I am using something like that:

public IEnumerable<IHierachyEntity> GetLifetimes(IEnumerable<IHierachyEntity> entities)
{
    if(entities is IEnumerable<Lifetime>)
    {
        return entities;
    }
    else
    {
        return GetLifetimes(entities.SelectMany(x => x.Childs))
    }
}

Upvotes: 2

Views: 295

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109253

Since this probably is a pretty fixed hierarchy in the heart of your application I wouldn't mind writing a dedicated piece of code for it. Moreover, writing an efficient generic routine for hierarchical queries is impossible with LINQ to a database backend. The n+1 problem just can't be avoided.

So just do something like this:

public IQueryable<Lifetime> GetLifetimes<T>(IQueryable<T> entities)
{
    var machines = entities as IQueryable<Machine>;
    if (machines != null)
        return machines.SelectMany (m => m.Items)
                       .SelectMany (i => i.Tools)
                       .SelectMany (i => i.Parts)
                       .SelectMany (i => i.Lifetimes);

    var items = entities as IQueryable<Item>;
    if (items != null)
        return items.SelectMany (i => i.Tools)
                    .SelectMany (i => i.Parts)
                    .SelectMany (i => i.Lifetimes);

    var tools = entities as IQueryable<Tool>;
    if (tools != null)
        return tools.SelectMany (i => i.Parts)
                    .SelectMany (i => i.Lifetimes);

    var parts = entities as IQueryable<Part>;
    if (parts != null)
        return parts.SelectMany (i => i.Lifetimes);

    return Enumerable.Empty<Lifetime>().AsQueryable();
}

Repetitive code, yes, but its is crystal clear what happens and it's probably among the most stable parts of the code. Repetitive code is a potential problem when continuous maintenance is to be expected.

Upvotes: 1

dblk1965
dblk1965

Reputation: 132

As much as I understood you trying to pull very long history of your actions. I need create a routine which will update your statistics as changes happened. This has no "ultimate" solution your should figure it out. E.g. I have "in" and "out" stock transactions and to find out current stock level for all items I should go through 20 years history. To come around I can do monthly summaries and only calculate changes from month start. Or I can use a database trigger to update my summaries as soon as changes happened (could be performance costly one). Or I can have a service that will update it time to time ( would not be 100% up to date possibly). In another words you need table/class which will keep your aggregated results ready to use.

Upvotes: 1

Related Questions