I want to create a hierarchy tree of employees and manager in JSON output using C#(ASP.NET Web Api).I have used EntityFramework.

My get api code is -

[Route("empLevel/{id}")]
public IActionResult GetEmpHierarchy(int id)
{
    try
    {
        if (id == 0)
        {
            return NotFound();
        }
        var query_hierarchy = entity.EmployeeHierarchies.FromSqlInterpolated($"EXECUTE dbo.FindHierarchy {id}").ToList();
        
        return Ok(query_hierarchy);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.StackTrace);
    }
    return NotFound();
}

The code for class EmployeeHierarchy is-

public class EmployeeHierarchy
    {

            public int Id { get; set; }
            public int? MngId { get; set; }
            public string Path { get; set; }
            public string Name { get; set; }
            public string FullPath { get; set; }
            public int Level { get; set; }
            public double Grade { get; set; }
    }

I have used CTE in a procedure for getting output from database which is as-

ALTER PROCEDURE [dbo].[FindHierarchy]
@empId int = null
AS
BEGIN
    with cte as(
                select Id, MngId, CAST('' AS VARCHAR(40)) ManagerName, Path = cast('' as varchar(40)),Name, Level = 0,Grade       
                from Employee    
                where MngId is null

                union all 

                select EmpId     =  e.Id, ParentId  =  e.MngId,
                        CAST(cte.Name AS VARCHAR(40)) ManagerName,
                        Path      =  cast(concat(cte.Path, cte.Name, ' -› ') as varchar(40)),
                        Name      =  e.Name,        
                        Level     =  cte.Level   1,
                        Grade = e.Grade
                    from 
                        Employee e
                        inner join cte on cte.Id = e.MngId
            )
                select 
                    Id      ,
                    MngId  ,
                    ManagerName,
                    Path       ,
                    Name       ,
                    FullPath   =  Path   Name,    
                    Level, Grade        
                from cte where ID=@empId
                order by FullPath   ;
END

Currently my output is as:

[
   {
      "id":2,
      "mngId":3,
      "name":"Blake",
      "fullPath":"Diana › Chelsa › Blake",
      "level":2,
      "grade":3.2
   }
]

I want to show it as-

    [
   {
      "id":2,
      "mngId":3,
      "name":"Diana",
      "level":2,
      "grade":5.2[
         {
            "id":2,
            "mngId":3,
            "name":"Chelsa",
            "level":2,
             "grade":5.1[
               {
                  "id":2,
                  "mngId":3,
                  "name":"Diana",
                  "level":2,
                  "grade":3.2[]
                }
             ]
        }
    ]  
  }
]
                 

I am new in asp.net web api, please suggest some code to my query. Thank you!!

CodePudding user response：

Your EmployeeHierarchy should look like this

public class EmployeeHierarchy
{

        public int Id { get; set; }
        public int? MngId { get; set; }
        public string Path { get; set; }
        public string Name { get; set; }
        public string FullPath { get; set; }
        public int Level { get; set; }
        public double Grade { get; set; }
        public EmployeeHierarchy ChildEmployee { get; set; }
}

That is the only way I can see. You must edit your SQL to match this model too.