hello,
how to achive this in linQ
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
