Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would set up a view and an associated table-based function based on the CTE. My reasoning for this is that, while you could implement the logic on the application side, this would involve sending the intermediate data over the wire for computation in the application. Using the DBML designer, the view translates into a Table entity. You can then associate the function with the Table entity and invoke the method created on the DataContext to derive objects of the type defined by the view. Using the table-based function allows the query engine to take your parameters into account while constructing the result set rather than applying a condition on the result set defined by the view after the fact.</p> <pre><code>CREATE TABLE [dbo].[hierarchical_table]( [id] [int] IDENTITY(1,1) NOT NULL, [parent_id] [int] NULL, [data] [varchar](255) NOT NULL, CONSTRAINT [PK_hierarchical_table] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] CREATE VIEW [dbo].[vw_recursive_view] AS WITH hierarchy_cte(id, parent_id, data, lvl) AS (SELECT id, parent_id, data, 0 AS lvl FROM dbo.hierarchical_table WHERE (parent_id IS NULL) UNION ALL SELECT t1.id, t1.parent_id, t1.data, h.lvl + 1 AS lvl FROM dbo.hierarchical_table AS t1 INNER JOIN hierarchy_cte AS h ON t1.parent_id = h.id) SELECT id, parent_id, data, lvl FROM hierarchy_cte AS result CREATE FUNCTION [dbo].[fn_tree_for_parent] ( @parent int ) RETURNS @result TABLE ( id int not null, parent_id int, data varchar(255) not null, lvl int not null ) AS BEGIN WITH hierarchy_cte(id, parent_id, data, lvl) AS (SELECT id, parent_id, data, 0 AS lvl FROM dbo.hierarchical_table WHERE (id = @parent OR (parent_id IS NULL AND @parent IS NULL)) UNION ALL SELECT t1.id, t1.parent_id, t1.data, h.lvl + 1 AS lvl FROM dbo.hierarchical_table AS t1 INNER JOIN hierarchy_cte AS h ON t1.parent_id = h.id) INSERT INTO @result SELECT id, parent_id, data, lvl FROM hierarchy_cte AS result RETURN END ALTER TABLE [dbo].[hierarchical_table] WITH CHECK ADD CONSTRAINT [FK_hierarchical_table_hierarchical_table] FOREIGN KEY([parent_id]) REFERENCES [dbo].[hierarchical_table] ([id]) ALTER TABLE [dbo].[hierarchical_table] CHECK CONSTRAINT [FK_hierarchical_table_hierarchical_table] </code></pre> <p>To use it you would do something like -- assuming some reasonable naming scheme:</p> <pre><code>using (DataContext dc = new HierarchicalDataContext()) { HierarchicalTableEntity h = (from e in dc.HierarchicalTableEntities select e).First(); var query = dc.FnTreeForParent( h.ID ); foreach (HierarchicalTableViewEntity entity in query) { ...process the tree node... } } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHierarchical data in Linq - options and performance
      singulars
      1. PTQuestion
    1. PTAnswer
    1. UStvanfosson
    1. UStvanfosson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHierarchical data in Linq - options and performance
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI have tried out a function like this, and it seems to be the way to go. And it can be called from LINQ, attached to the datacontext. Also, why both the view and the function? - they seem to be duplication
      singulars
      1. PO
      1. USAnthony
    2. COThe function doesn't map the same schema as the table. It includes the level. If you didn't have the added column, you could map it directly onto the table. I assumed level in the hierarchy was important.
      singulars
      1. PO
      1. UStvanfosson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload