I have a complex hierarchy and I have used RecursiveJoin Extension method and it returned to me a tree structure with parents nodes, children nodes and depth.So it's ok but I need to calculate total score point from ScoreItem table from database. As well as I had wrote this simple calculation method in t-sql at below,
Begin
Select
sp.Name,
u.FirstName + ' ' + u.LastName as NameAndSurname,
rgc.Name as ScoreCard,
si.TotalPoint
from Score s
inner join ScoreItem si on si.ScoreId = s.Id
inner join ProjectResearchGroup prg on si.ProjectResearchGroupId = prg.Id
inner join RgClone rgc on prg.RgCloneId = rgc.Id
inner join Salespoint sp on s.SalesPointId = sp.Id
inner join SalesHierarchySalesPoint shsp on sp.Id = shsp.SalesPointId
inner join SalesHierarchy sh on shsp.SalesHierarchyId = sh.Id
inner join [User] u on u.Id = sh.CompanyResponsibleId
where sh.Id in
(Select Id
from SalesHierarchy
where ParentId in
(Select Id
from SalesHierarchy
where ParentId in
(Select Id
from SalesHierarchy
where ParentId in
(Select Id
from SalesHierarchy
where CompanyId = 2
and ParentId is null))))
and si.IsValidForSalesPoint = 1
End
Also this my back end code for try to implement to linq query from above t-sql queries.
IEnumerable<FlatData> _SalesHierarchy = db.SalesHierarchy
.Select(sh =>
new FlatData()
{
Id = sh.Id,
ParentId = sh.ParentId ?? 0,
Text = sh.Name
})
.ToList();
IEnumerable<DeepNodeData> nodes = _SalesHierarchy.RecursiveJoin(e => e.Id, e => e.ParentId,
(FlatData sh, int index, int depth, IEnumerable<DeepNodeData> children) =>
{
return new DeepNodeData
{
Id = sh.Id,
ParentId = sh.ParentId,
Text = sh.Text,
Children = children,
Depth = depth
};
As a result these above codes return me, this result,
So I would like to return Json output for my web api,such as these
"data": [
{
"level1": "XXXX,XXXX,XXXX",
"level2": "XXXX,XXXX,XXX",
"level3": "XXXX,XXXX,XX",
"level4": "XXXX,XXXX,X",
"val": 2
},
]
};
How can I implement hierarchy query with my other linq queries and return above Json format. if you have any suggestion and sample application about it, please may you share with me,