1

I have the following SQL table:

 ObjectTable
--------------------------------------------------
|  ID      |  Name         |  Order  |  ParentID  |
|  int PK  |  nvarchar(50) |  int    |  int FK    |

ObjectTable.ParentID is a nullable field with a relationship to a different Object record's ID. LINQ-to-SQL generates an class that looks like:

 public class DbObject{

      int ID { get; set; }

      string Name { get; set; }

      int Order { get; set; }

      int? ParentID { get; set; }

      DbObject Parent { get; set; }

      EntitySet<DbObject> ChildObjects { get; set; }

 }

When I load a DbObject instance, I need to be able to recursively access the child objects, so that I can write the data to a hierarchical JSON object.

  • Will I execute a separate query everytime I access the elements via DbObject.ChildObjects? Since DB transactions take the longest, it seems like this is a very inefficient way to load a recursive hierarchy.

  • What is the best practice for executing a recursive query with LINQ-to-SQL and/or EF?

  • Is LINQ-to-SQL integrated with Common Table Expressions? I found Common Table Expression (CTE) in linq-to-sql?

Community
  • 1
  • 1
smartcaveman
  • 41,281
  • 29
  • 127
  • 212
  • Are you in a position to change the data model? If not, then I would think you need to go down the sql string execution path mentioned in the other question you reference. – Chris Sainty Jul 06 '11 at 02:53
  • @chrissainty, I could change the data model. Are you saying there is no situation where a hierarchical data model is appropriate? – smartcaveman Jul 06 '11 at 03:02
  • Not saying it is inappropriate. Just it won't work with std LINQ queries. So you could change the model to make it a single LINQ query, or you could do an sproc/text query. Just depends on which way you prefer. – Chris Sainty Jul 06 '11 at 05:42

1 Answers1

3

in our case we have created stored procedures with CTE's and put them on the l2s designer and they work like any other Table except they behave as a method rather than property and we have experienced no problem with that so far.

Muhammad Adeel Zahid
  • 17,474
  • 14
  • 90
  • 155
  • This is correct answer. Neither Linq-to-sql or EF has support for efficient recursive queries. Use native SQL with CTEs for that. – Ladislav Mrnka Jul 06 '11 at 07:08