CTE with graph trees
Posted: Wed 13 May 2020 20:22
Hello
I have this CTE request working under SSMS :
but, in the table, this column $node_id is in fact called '$node_id_39671D6AD91E4DBD8E43597C9E86C7BF'. So DevArt EntityDev generate this virtual function :
public virtual string NodeId39671D6AD91E4DBD8E43597C9E86C7BF (); [..]
so :
1) the random string after NodeId will be different if we recreate the database, that can be a problem.
2) It is possible to convert this CTE into Linq query ?
Thanks for help.
Vincent
I have this CTE request working under SSMS :
Code: Select all
-- List all childs (v3 - function)
with rCTE AS
(
-- Start node
SELECT r2.FullName AS Output,r1.$node_id AS parentid, r2.$node_id as bottomnode,1 as Tree
FROM Animal r1
JOIN ChildGenealogyLink e ON e.$from_id = r1.$node_id
JOIN Animal r2 ON r2.$node_id = e.$to_id
AND r1.FullName IN ('F''Sheree Jones Of Dawson City')
--AND r1.BreederId = 1
UNION ALL
-- Recurse part
SELECT r.FullName,c.bottomnode,r.$node_id,Tree + 1
FROM rCTE c
JOIN ChildGenealogyLink e ON e.$from_id = c.bottomnode
JOIN Animal r ON r.$node_id = e.$to_id
)
SELECT output AS 'Full name' FROM rCTE
OPTION (MAXRECURSION 1000); -- Set max recursion level
public virtual string NodeId39671D6AD91E4DBD8E43597C9E86C7BF (); [..]
so :
1) the random string after NodeId will be different if we recreate the database, that can be a problem.
2) It is possible to convert this CTE into Linq query ?
Thanks for help.
Vincent