【.net core】yisha框架 SQL SERVER数据库 反向递归查询部门（子查父）

 业务service.cs中ListFilter方法中内容

//反向递归查询部门列表
                    List<DepartmentEntity> departmentList = await departmentService.GetReverseRecurrenceList(new DepartmentListParam() { Ids = operatorInfo.DepartmentId.ToString() });
                    if (departmentList != null && departmentList.Count > 0)
                    {
                        //查找列表中为根节点的部门
                        DepartmentEntity department = departmentList.Find(a => a.ParentId == 0);
                        if (department != null)
                        {
                            strSql.Append(" AND a.AffiliatedUnit = @AffiliatedUnit");
                            parameter.Add(DbParameterExtension.CreateDbParameter("@AffiliatedUnit", department.Id));
                        }

                    }

DepartmentService.cs中内容

public async Task<List<DepartmentEntity>> GetReverseRecurrenceList(DepartmentListParam param)
        {
            var strSql = new StringBuilder();
            List<DbParameter> filter = ListFilter(param, strSql);
            var list = await this.BaseRepository().FindList<DepartmentEntity>(strSql.ToString(), filter.ToArray());
            return list.ToList();
        }

private List<DbParameter> ListFilter(DepartmentListParam param, StringBuilder strSql, bool bNewsContent = false)
        {
            //param.ids需为单个ID
            strSql.Append($@"with temp as 
                ( select * from SysDepartment where id = {(!string.IsNullOrEmpty(param.Ids) ? param.Ids : "0")}
                union all select a.* from SysDepartment a inner join temp on temp.[parentId]  =  a.[id]) 
                select * from temp");
            var parameter = new List<DbParameter>();
            //strSql.Append(@" ORDER BY cast(ISNULL(BuildingNumber, '0') as int) asc,cast(ISNULL(Floor, '0') as int) asc,HouseCode asc ");
            return parameter;
        }

mysql数据库中参考mysql 递归语法修改sql