how can I code a recursive query in an Entity Framework model?

Question

Hi,

I have a model which includes NODES, and RELATIONSHIPS (that tie the nodes together, via a parent_node, child_node arrangement).

Q1 - Is there any way in EF / Linq-to-entities to perform a query on nodes (e.g. context.Nodes..) to find say "all parents" or "or children" in the graph?

Q2 - If there's not in Linq-to-entities, is there any other way to do this other than writing a method that manually goes through and doing it?

Q3 - If manual is the only way to do it, should I be concerned about the number of database hits that will be going out to the database as the method keeps recursing through the data? Or more specifically, is there any EF caching type feature that might assist here in ensuring the method is performance from a "number of database hits" point of view?

thanks

thanks

Answer 1

There is no such elegant way to flaten a tree. you can (in stored proc or in entity framework) create a loop that will run untill no change happend, in which iteration you will bind parent and child's child in some temp table or collection. in the end you will have a collection of parents, sucsessor two-ples.

Answer 2

When you are using Microsoft SQL Server 2005 or up, you can use CTEs (Common Table Expressions). They allow you do define a recursive query. While SQL Server under the covers doesn't do much more than firing a bunch of queries for you, it does this completely server side, so it saves you from having a lot of client-server communication.

You will have to do this using a stored proc or a normal SQL query, because there is no way EF can do this for you.