una tablas query que jerarquico jerarquicas jerarquia hijo estructura ejemplos datos consulta con bases arbol sql linq sql-server-2005 c#-3.0 common-table-expression

tablas - query jerarquico sql



Datos jerárquicos en Linq-opciones y rendimiento (9)

Tengo algunos datos jerárquicos: cada entrada tiene una identificación y una identificación de entrada principal (nulable). Quiero recuperar todas las entradas en el árbol debajo de una entrada determinada. Esto está en una base de datos SQL Server 2005. Lo estoy consultando con LINQ to SQL en C # 3.5.

LINQ to SQL no admite expresiones comunes de tabla directamente. Mis opciones son ensamblar los datos en código con varias consultas LINQ, o hacer una vista en la base de datos que surja un CTE.

¿Qué opción (u otra opción) crees que funcionará mejor cuando los volúmenes de datos sean grandes? ¿Es compatible el tipo HierarchyId de SQL Server 2008 en Linq con SQL?

En MS SQL 2008 puede usar HierarchyID directamente, en sql2005 puede que tenga que implementarlos manualmente. ParentID no es tan eficiente en grandes conjuntos de datos. También consulte este artículo para más discusión sobre el tema.

He hecho esto de dos maneras:

  1. Dirija la recuperación de cada capa del árbol según la entrada del usuario. Imagine un control de vista de árbol poblado con el nodo raíz, los elementos secundarios de la raíz y los nietos de la raíz. Solo la raíz y los hijos se expanden (los nietos se ocultan con el colapso). A medida que el usuario expande un nodo secundario, se muestran los nietos de la raíz (que se recuperaron y ocultaron previamente), y se lanza una recuperación de todos los bisnietos. Repita el patrón para N capas profundas. Este patrón funciona muy bien para árboles grandes (profundidad o ancho) porque solo recupera la parte del árbol necesaria.
  2. Use un procedimiento almacenado con LINQ. Utilice algo así como una expresión de tabla común en el servidor para generar sus resultados en una tabla plana, o cree un árbol XML en T-SQL. Scott Guthrie tiene un excelente artículo sobre el uso de procs almacenados en LINQ. Cree su árbol a partir de los resultados cuando regresen si está en un formato plano, o use el árbol XML si eso es lo que devuelve.

Obtuve este enfoque desde el blog de Rob Conery (revise alrededor de Pt. 6 para este código, también en Codeplex) y me encanta usarlo. Esto podría remodelarse para admitir múltiples niveles "secundarios".

var categories = from c in db.Categories select new Category { CategoryID = c.CategoryID, ParentCategoryID = c.ParentCategoryID, SubCategories = new List<Category>( from sc in db.Categories where sc.ParentCategoryID == c.CategoryID select new Category { CategoryID = sc.CategoryID, ParentProductID = sc.ParentProductID } ) };

Configuraría una vista y una función asociada basada en tablas basada en el CTE. Mi razonamiento para esto es que, si bien podría implementar la lógica en el lado de la aplicación, esto implicaría enviar los datos intermedios a través del cable para el cálculo en la aplicación. Usando el diseñador de DBML, la vista se traduce en una entidad de tabla. A continuación, puede asociar la función con la entidad Tabla e invocar el método creado en el DataContext para derivar objetos del tipo definido por la vista. El uso de la función basada en tablas permite que el motor de consultas tenga en cuenta los parámetros al construir el conjunto de resultados en lugar de aplicar una condición en el conjunto de resultados definido por la vista después del hecho.

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]

Para usarlo, harías algo así como: asumir un esquema de nombres razonable:

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... } }

El problema de obtener los datos del lado del cliente es que nunca puede estar seguro de qué tan profundo debe ir. Este método hará un viaje de ida y vuelta por profundidad y se puede unir para hacer desde 0 hasta una profundidad específica en una ida y vuelta.

public IQueryable<Node> GetChildrenAtDepth(int NodeID, int depth) { IQueryable<Node> query = db.Nodes.Where(n => n.NodeID == NodeID); for(int i = 0; i < depth; i++) query = query.SelectMany(n => n.Children); //use this if the Children association has not been defined //query = query.SelectMany(n => db.Nodes.Where(c => c.ParentID == n.NodeID)); return query; }

No puede, sin embargo, hacer profundidad arbitraria. Si realmente necesita una profundidad arbitraria, debe hacer eso en la base de datos para que pueda tomar la decisión correcta de detenerse.

Este método de extensión podría ser modificado para usar IQueryable. Lo he usado con éxito en el pasado en una colección de objetos. Puede funcionar para su escenario.

public static IEnumerable<T> ByHierarchy<T>( this IEnumerable<T> source, Func<T, bool> startWith, Func<T, T, bool> connectBy) { if (source == null) throw new ArgumentNullException("source"); if (startWith == null) throw new ArgumentNullException("startWith"); if (connectBy == null) throw new ArgumentNullException("connectBy"); foreach (T root in source.Where(startWith)) { yield return root; foreach (T child in source.ByHierarchy(c => connectBy(root, c), connectBy)) { yield return child; } } }

Así es como lo llamé:

comments.ByHierarchy(comment => comment.ParentNum == parentNum, (parent, child) => child.ParentNum == parent.CommentNum && includeChildren)

Este código es una versión mejorada y corregida de errores del código que se encuentra aquí .

Me sorprende que nadie haya mencionado un diseño de base de datos alternativo: cuando la jerarquía necesita ser nivelada desde múltiples niveles y recuperada con alto rendimiento (no considerando el espacio de almacenamiento), es mejor usar otra tabla entity-2-entity para rastrear la jerarquía en lugar de parent_id enfoque.

Permitirá no solo relaciones de padres solteros sino también relaciones de múltiples padres, indicaciones de nivel y diferentes tipos de relaciones:

CREATE TABLE Person ( Id INTEGER, Name TEXT ); CREATE TABLE PersonInPerson ( PersonId INTEGER NOT NULL, InPersonId INTEGER NOT NULL, Level INTEGER, RelationKind VARCHAR(1) );