public object MaxInTree(IConnectionProvider conn, IEntity rootEntity, IDbColumn column, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Max(column, null));
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            DataTable result = cte.ExecuteSelect(conn);
            return result.Rows[0][0];
        }
        public int CountTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, AggregateFunctionFactory.Count(null));
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            DataTable result = cte.ExecuteSelect(conn);
            int count = Convert.ToInt32(result.Rows[0][0], CultureInfo.InvariantCulture);

            return count;
        }
        public int UpdateTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, UpdateList setExpressions, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            // update target set ... where exists ... and ...
            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Update = new UpdateStatement(rootEntity.Table);
            cte.Update.UpdateList = setExpressions;
            cte.Update.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);

            int rowsAffected = cte.ExecuteUpdate(conn);
            return rowsAffected;
        }
        public int DetermineTreeDepth(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation)
        {
            EnsureRecursiveRelation(recursiveRelation);

            // select max(level) as maxLevelIndex from hierarchy
            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(cte.Hierarchy, AggregateFunctionFactory.Max(cte.Hierarchy.Level, null));

            DataTable results = cte.ExecuteSelect(conn);
            int highestLevel = (results.Rows[0][0] != DBNull.Value) ? Convert.ToInt32(results.Rows[0][0], CultureInfo.InvariantCulture) : 0;
            int depth = highestLevel + 1;

            return depth;
        }
        public DataTable SelectTree(IConnectionProvider conn, IEntity rootEntity, DbRelation recursiveRelation, SearchCondition leafFilter, OrderByClause sorter, bool sortOnDb, int beginAtLevel, int endAtLevel)
        {
            EnsureRecursiveRelation(recursiveRelation);
            EnsureValidBeginAndEndLevels(beginAtLevel, endAtLevel);

            // select target.* from target where exists ... and ...
            RecursiveStatement cte = new RecursiveStatement(rootEntity, recursiveRelation);
            cte.Select = new SelectStatement(rootEntity.Table, rootEntity.Table.Columns);
            cte.Select.Where = CreateWhere(rootEntity, cte, leafFilter, beginAtLevel, endAtLevel);
            if (sortOnDb && !OrderByClause.IsNullOrEmpty(sorter))
                cte.Select.OrderBy = sorter;

            DataTable data = cte.ExecuteSelect(conn);
            if (!sortOnDb && !OrderByClause.IsNullOrEmpty(sorter))
                data = GenericHierarchicalQueryExecutor.SortDataTable(data, sorter);

            return data;
        }
        private static SearchCondition CreateWhere(IEntity rootEntity, RecursiveStatement cte, SearchCondition leafFilter, int beginAtLevel, int endAtLevel)
        {
            // where exists (select hierarchy.id from hierarchy where hierarchy.id = target.id and level betwen beginAtLevel and endAtLevel)
            // and status = ...
            SelectStatement existsInHierarchy = new SelectStatement(cte.Hierarchy, cte.GetHierarchyPK());
            foreach (IDbColumn targetPkField in rootEntity.Table.PrimaryKey)
            {
                IDbColumn hierarchyPkField = cte.Hierarchy.Columns.GetByColumnName(targetPkField.ColumnName);
                existsInHierarchy.Where.Add(PredicateFactory.Compare(hierarchyPkField, "=", targetPkField));
            }

            bool hasLevelConstraints = (beginAtLevel > 0) || (endAtLevel < int.MaxValue);
            if (hasLevelConstraints)
                existsInHierarchy.Where.Add(PredicateFactory.Between(cte.Hierarchy.Level, beginAtLevel, endAtLevel));

            SearchCondition where = (SearchCondition)PredicateFactory.Exists(existsInHierarchy);
            if (leafFilter != null && !leafFilter.IsEmpty)
                where.Add(leafFilter);

            return where;
        }