Hierarchical data in a drop-down list on Dynamic Data site

I'm getting into dynamic data sites (remarkably powerful), and enjoying it. However, I have a requirement that I can't seem to meet. I have a self-joined category table (Id, Name, ParentId) to create a hierarchical category structure (e.g. the categories with null ids are top level, usual sort of thing). I then have a products table that has a foreign key to the category.

Within the dynamic data site, I have the category dropdown list automatically generated; however it just lists all categories in numerical order. What I would like (and what I've coded before, pre-dynamic data) is an indented list, hierarchically arranged list of categories.

Do I replace the existing FilterUserControl? Can I override the PopulateListControl method anywhere? Does anyone have any LINQ syntax for extract self-joined hierarchical data?

Just pointers and advice would do, thank you for any help offered.

Kind regards,

Mike Kingscott

Solution

In Oracle:

SELECT  LEVEL, Id, Name, LPAD(' ', LEVEL) || Name AS IndentedName
FROM    Categories
START WITH
        ParentID IS NULL
CONNECT BY
        ParentID = PRIOR Id
ORDER SIBLINGS BY
        Name

You may use IndentedName or make you custom formatting based on LEVEL pseudocolumn (it shows depth of each category)

P.S. It's a bad idea to use NULL as the top parent ID, as you cannot use index to access it. Use a 0 instead.

Update:

In SQL Server:

WITH    q (id, parentid, name, level, bc) AS
        (
        SELECT  id, parentid, name, 1, CAST(ROW_NUMBER() OVER (ORDER BY name) AS VARCHAR(MAX))
        FROM    Categories
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  c.id, c.parentid, c.name, q.level + 1, q.bc + '.' + CAST(ROW_NUMBER() OVER (ORDER BY c.name) AS VARCHAR(MAX))
        FROM    q
        JOIN    Categories c
        ON      c.parentId = q.id
        )
SELECT  *
FROM    q
ORDER BY
        bc

Unlike Oracle, SQL Server indexes NULL values, so it's possible to use a NULL to mark an ultimate ancestor.