CTE with graph trees

[nolme]

Hello

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

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

[Shalex]

Graph tables are supported in SQL Server 2019 only by Entity Developer starting from v6.6.936:
a) you are working with SQL Server 2019, aren't you?
b) make sure that your Entity Developer version is 6.6.936 or higher. If not, upgrade Entity Developer and recreate the model

1) the random string after NodeId will be different if we recreate the database, that can be a problem.

1. The new names of columns after recreating database are generated by SQL Server, so Entity Developer cannot control this behavior. We will look for a way to detect and truncate the "GUID" suffix and will notify you about the result.

2) It is possible to convert this CTE into Linq query ?

2. CTE queries could not be created with LINQ.

[nolme]

Hello,

a) yes, it's 15.0.2000.5 version.
b) I've just installed 6.7.999.0

1) Ok. I've opened a question on StackOverflow (https://stackoverflow.com/questions/619 ... 7c9e86c7bf) to see if someone knows how SSMS map '$node_id' in T-SQL to $node_id_39671D6AD91E4DBD8E43597C9E86C7BF' in database.

2) Ok. So my workaround is to create a function in SQL Server and call it throw DevArt ED like explained here :
https://entityframework.net/knowledge-b ... ine-syntax