LINQ. Is this select possible?

[shapper]

Hello,

I created an SQL table using the Adjacency Model described here:
http://www.sqllessons.com/categories.html

By using this model I am able to hold many categories and
subcategories in one table.

I then need to create a Site Map using <uland <liHTML tags.

I am trying to solve 2 problems, using LINQ:

1. Get all child nodes given the ID of a certain Node.

2. Get all categories and subcategories in one query.

Could someone help me doing this with LINQ?

Thanks,
Miguel

[Marc Gravell]

Well, I'm not sure that LINQ will make it any easier, as it will
probably want to walk the children in additional queries.

Actually, I'm not sure this is the way I'd store the data... firstly,
how about an xml column? great for hierarchical data. Alternatively,
perhaps change to a spanning-tree - i.e.

ParentNode Span

[Frans Bouma [C# MVP]]

shapper wrote:

Hello,

I created an SQL table using the Adjacency Model described here:
http://www.sqllessons.com/categories.html

By using this model I am able to hold many categories and
subcategories in one table.

I then need to create a Site Map using <uland <liHTML tags.

I am trying to solve 2 problems, using LINQ:

1. Get all child nodes given the ID of a certain Node.

2. Get all categories and subcategories in one query.

Could someone help me doing this with LINQ?

Don't use the adjacency model for storing trees in a table, as it's
hard to query a hierarchy.

You could solve your problem with a second table which actually
precalculates the hierarchy and with every update on yuor main model,
recalculates the hierarchy. Then you can with a single join obtain the
complete tree with predicates.

Another way to store a tree is using the CELKO way (weighted tree
model)

See for a discussion, SQL code on the precalc hierarchy and links to
CELKO's article here:
http://www.llblgen.com/tinyforum/Mes...?ThreadID=3208

after you've solved the hierarchy problem, the query you need is easy
to write.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Marc Gravell]

The links that Frans cites are a similar approach to the "Span" stuff I
mentioned, but they look cheaper to maintain - especially the sub-link
with the Lineage example... I'd go for that ;-p

Of course, as always you need to balance edit performance vs read
performance...

But either way, self-referential data isn't going to play friendly with
LINQ in a "get the entire subtree in one hit" kind of way... this is one
of those occasions where you need to just do it another way.

Marc