【.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需为单个IDstrSql.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