Question 231218 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – LINQ Query for below table

Table data:

Id_Org_hierarchy Hierarchy_Name Parent_Id_Org_hierarchy
1 OGt 0
2 NHT 1
3 BATCH 1 2
3 Voice 1

I need result Like Below:

Batch1(NHT,OGT)

Details :
Id_Org_hierarchy column value is present in Parent_Id_Org_hierarchy and so on

its a tree struct format data.
can any one help me for this I have no idea how to achieve result.

Expecting this result Batch1(NHT,OGT)

Tags:

2

Answers


  1. 0

    Have a class structure representing the table rows, the solution would involve a recursive method to traverse the tree and build the result.

    Here’s a basic solution:

    Define your class:

    public class OrgHierarchy
{
    public int Id_Org_hierarchy { get; set; }
    public string Hierarchy_Name { get; set; }
    public int Parent_Id_Org_hierarchy { get; set; }
}

    Use LINQ and recursion:

    public string GetHierarchyPath(List<OrgHierarchy> data, int id)
{
    var item = data.FirstOrDefault(x => x.Id_Org_hierarchy == id);
    if (item == null) return string.Empty;
    if (item.Parent_Id_Org_hierarchy == 0) return item.Hierarchy_Name;

    return item.Hierarchy_Name + "," + GetHierarchyPath(data, item.Parent_Id_Org_hierarchy);
}

public void RunExample()
{
    List<OrgHierarchy> list = new List<OrgHierarchy>
    {
        new OrgHierarchy { Id_Org_hierarchy = 1, Hierarchy_Name = "OGt", Parent_Id_Org_hierarchy = 0 },
        new OrgHierarchy { Id_Org_hierarchy = 2, Hierarchy_Name = "NHT", Parent_Id_Org_hierarchy = 1 },
        new OrgHierarchy { Id_Org_hierarchy = 3, Hierarchy_Name = "BATCH 1", Parent_Id_Org_hierarchy = 2 },
        new OrgHierarchy { Id_Org_hierarchy = 4, Hierarchy_Name = "Voice", Parent_Id_Org_hierarchy = 1 }
    };

    var item = list.FirstOrDefault(x => x.Id_Org_hierarchy == 3); // assuming we're starting from id 3
    if (item != null)
    {
        var path = GetHierarchyPath(list, item.Parent_Id_Org_hierarchy);
        var result = $"{item.Hierarchy_Name}({path.TrimEnd(',')})";
        Console.WriteLine(result); // Outputs: BATCH 1(NHT,OGt)
    }
}

    Run the RunExample() method to see the result.

    Login or Signup to reply.
  2. 0

    You will need to use multiple LINQ queries or entity framework calls that each finds a parent of the element, like this:

    current = initial; // initial is the very first item, Batch1 in this case
while ((current != null) && (current.Parent_Id_Org_hierarchy > 0)) {
    current = dbo.Where(item => item.Id_Org_hierarchy == current.Parent_Id_Org_hierarchy);
    if (current != null) myList.Add(current);
}

    and then you can generate the string you wanted by putting initial‘s Hierarchy_name before the paranthesis and then wrap a paranthesis around the comma-separated concatenation of the Hierarchy_name values of myList via

    string.Join(",", myList.Select(item => item.Hierarchy_name))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search