LINQ to SQL hierarchy problem

I retrieve a result from SQL Server as

ProjectDetailID,ProjectID,ParentID,...,C1,C2,C3,...

where C1 implies(=>) companyOne, C2=>CompanyTwo ... etc and dynamically can have 'n' companies

For time being lets consider only 3 companies, So I get :

ProjectDetailID,ProjectID,ParentID,C1,C2,C3
10,1,0,NULL,NULL,NULL
10,2,1,NULL,NULL,NULL
10,3,2,90,NULL,NULL
10,4,2,NULL,60,NULL
10,10,1,70,NULL,NULL开发者_运维问答
10,5,10,20,40,NULL
10,13,2,NULL,NULL,NULL

I want from this following result using LINQ (C#)

ProjectDetailID,ProjectID,ParentID,C1,C2,C3
10,1,0,180,100,NULL
10,2,1,90,60,NULL
10,3,2,90,NULL,NULL
10,4,2,NULL,60,NULL
10,10,1,90,40,NULL
10,5,10,20,40,NULL
10,13,2,NULL,NULL,NULL

The problem is that at parent level i have null value for a company but at its child i have some value, which i keep on adding and have placed that in parent corresponding to that company only.

I am not getting from where to start. Please share your ideas. And i am looking to do this in LINQ using C#

---

I think I have a solution to your problem.

I've assumed that the Project class has a constructor that enables me to do this:

var projects = new List<Project>(new []
{
    new Project(10, 1, 0, null, null, null),
    new Project(10, 2, 1, null, null, null), 
    new Project(10, 3, 2, 90, null, null), 
    new Project(10, 4, 2, null, 60, null), 
    new Project(10, 10, 1, 70, null, null), 
    new Project(10, 5, 10, 20, 40, null), 
    new Project(10, 13, 2, null, null, null), 
});

So then the solution looks like this:

var traverse = projects.ToLookup(p => p.ParentID);

Func<Project, Func<Project, int?>, int?> rollup = null;

rollup = (p, f) =>
{
    var result = (f(p) ?? 0) + traverse[p.ProjectID].Sum(p2 => rollup(p2, f));
    return result == 0 ? (int?)null : result;
};

var query = 
    projects.Select(p =>
        new Project(
            p.ProjectDetailID,
            p.ProjectID,
            p.ParentID,
            rollup(p, p2 => p2.C1),
            rollup(p, p2 => p2.C2),
            rollup(p, p2 => p2.C3)));

The trick to this query is the recursive definition of rollup. For it to work the anonymous function must be declared and defined using two statements (ie the definition cannot be inline).

The result matches your second set of data in your question. Hopefully this is what you're after.