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 <ul> and <li> HTML 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

 

[Marc Gravell]

Darn - sent too early...

What I was trying to say is that are alternative ways of holding trees
that don't require you to work recursively (but with more complicated
maintenance) - i.e.

A
B
C
D
E

is: numbering each Node in order, don't know the Span yet:
A (1,?)
B (2,?)
C (3,?)
D (4,?)
E (5,?)

and then working upwards from leaf-nodes, finding the maximum span
from the children each time:

A (1,5)
B (2,2)
C (3,4)
D (4,4)
E (5,5)

Which can be represented in a single table:

Parent | Node | Span | Key
-1 1 5 A
1 2 2 B
1 3 4 C
3 4 4 D
1 5 5 E

Now to get all sub-nodes of any node, you simply get the Span for the
Node in question, and select all nodes where Node BETWEEN Node and
Span - i.e. the (inclusive) subtree of C is 3-4, i.e. C & D; the
subtree of A is 1-5, etc. For the strict (exclusive) subtree just
exclude Node from that range.


Finally, if you can work with xml but want to keep your existing
structure, you can get SQL-Server to build recursive trees for you;
I'll dig out an example...

Marc

 

[Marc Gravell]

Recursive xml-query approach (just one way of doing it... note that
you can use LINQ-to-SQL to make a udf directly callable):


set nocount on
-- cleanup (ignore first time)
drop table tree
drop function fnSubTree
drop function fnTree

-- schema (TODO: add PK/FK)
create table tree (id int not null identity(1,1), parent int null,
[key] varchar(10) not null, [order] int)

-- demo data
declare @root int
insert tree values (null, 'A', 1)
set @root = scope_identity()
insert tree values (@root, 'B', 1)
insert tree values (@root, 'E', 3)
insert tree values (@root, 'C', 2)
insert tree values (scope_identity(), 'D', 1)
go
-- declare root func
create function dbo.fnTree (@key varchar(10)) returns xml
as
begin
return (
select [key] as [@key], dbo.fnSubTree(id)
from tree
where [key] = @key
for xml path('node'), type)
end
go
-- declare recursive func
create function dbo.fnSubTree (@parent int) returns xml
as
begin
return (
select [key] as [@key], dbo.fnSubTree(id)
from tree
where parent = @parent
order by [order]
for xml path('node'), type)
end
go
-- examples
select dbo.fnTree('A')
select dbo.fnTree('C')

 

[Marc Gravell]

Oh - and not that I advocate mixing backend (sql) and UI (html), but
take it too far and you can get:

-- declare root func
create function dbo.fnTree (@key varchar(10)) returns xml
as
begin
return (
select [key] as [span], dbo.fnSubTree(id) as [ul]
from tree
where [key] = @key
for xml path('li'), root('ul'), type)
end
go
-- declare recursive func
create function dbo.fnSubTree (@parent int) returns xml
as
begin
return (
select [key] as [span], dbo.fnSubTree(id) as [ul]
from tree
where parent = @parent
order by [order]
for xml path('li'), type)
end
go

 

[shapper]

Oh - and not that I advocate mixing backend (sql) and UI (html), but
take it too far and you can get:

-- declare root func
create function dbo.fnTree (@key varchar(10)) returns xml
as
begin
return (
select [key] as [span], dbo.fnSubTree(id) as [ul]
from tree
where [key] = @key
for xml path('li'), root('ul'), type)
end
go
-- declare recursive func
create function dbo.fnSubTree (@parent int) returns xml
as
begin
return (
select [key] as [span], dbo.fnSubTree(id) as [ul]
from tree
where parent = @parent
order by [order]
for xml path('li'), type)
end
go

I am using an SQL table to hold categories instead of a XML file
because this is used to categorize documents.
Not only for the site map. The site map was just an example.

I have two other tables:
Documents > DocumentID, Title, Description
DocumentsCategories > DocumentID, CategoryID

Then I was using Adjacency Model to create the table Categories.
This way I could have a Document associated to one or more categories
and at the same time I could have various levels of categories.

But since I am using LINQ to SQL I would like to project my tables in
a way that would make it possible.

I still need to create the List Items, to create and delete
categories, and also to display them.

Basically I think I need pull the data to a class name Category which
would have a property of type List(Of Category).

So this is what I am trying to do.

Thanks,
Miguel

 

[Frans Bouma [C# MVP]]

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 <ul> and <li> HTML 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/Messages.aspx?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