hello,

how to achive this in linQ

Write Recursive query in SQL Server

sample data

category_id category_name parent_category_id
1   animal  NULL
2   vegetable   NULL
3   fruit   NULL
4   doggie  1
5   horsie  1
6   birdie  1
7   carrot  2
8   leafy   2
9   potato  2
10  celery  10
11  rutabaga    2
12  mango   3
13  orange  3
14  graps   3
15  apple   3
16  companion   4
17  herding 4
18  poodle  19
19  chihuahua   19
20  shepherd    20
21  collie  20

sql i have

WITH DirectReports (ID, PARENT_NAMEID,HLevel,NAME,Displayname)       
AS       
(       
    -- Anchor member definition       
    SELECT    cm.category_id,
            cd.parent_category_id,
            0 as HLevel,
            cast(cm.category_name as varchar(max)) as NAME,   
            cast(Right(10000 + (cast('0' as varchar(max)) + cast(cm.category_id as varchar(max))),4) as varchar(max)) as Displayname              
    FROM category_master cm
        INNER JOIN category_master cd ON cm.category_id =  cd.category_id
    WHERE cm.parent_category_id IS NULL
    
    UNION ALL       
     
    -- Recursive member definition       
     
    SELECT    cm.category_id,
            cd.parent_category_id,
            HLevel+1,
            cast((RIGHT('                                   ',(HLEVEL + 1) * 4) + isnull(cm.category_name,'')) as varchar(max)) as NAME,
            cast(cast(Displayname as varchar(max)) + cast(cm.category_id as varchar(max)) as varchar(max)) as Displayname 
     FROM category_master cm
            INNER JOIN category_master cd on cm.category_id =  cd.category_id
            INNER JOIN DirectReports d on cd.parent_category_id = d.ID     
             
)       
-- Statement that executes the CTE       
 
SELECT ID, PARENT_NAMEID,HLevel,[NAME],Displayname from DirectReports   
ORDER BY Displayname

output

ID  PARENT_NAMEID   HLevel  NAME    Displayname
1   NULL    0   animal  0001
4   1   1       doggie  00014
16  4   2           companion   0001416
17  4   2           herding 0001417
5   1   1       horsie  00015
6   1   1       birdie  00016
2   NULL    0   vegetable   0002
11  2   1       rutabaga    000211
7   2   1       carrot  00027
8   2   1       leafy   00028
9   2   1       potato  00029
3   NULL    0   fruit   0003
12  3   1       mango   000312
13  3   1       orange  000313
14  3   1       graps   000314
15  3   1       apple   000315