官网地址:树型递归查询 - SqlSugar 5x - .NET果糖网
现有一张地区区域表,数据如下:
如果要遍历出各个层级的名称,一般使用递归的方法去查询,在SqlSugar中,封装了相关方法,直接调用就可以轻松实现这种具有层级关系的数据查询。
实体类:
using SqlSugar;
using System;
using System.Collections.Generic;
namespace SqlSugarStart.DbModels
{
/// <summary>
/// 区域信息
/// </summary>
[SugarTable("Region")]
public class Region
{
[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
public int Id { get; set; }
public string Name { get; set; }
public int? ParentId { get; set; }
public DateTime? CreateTime { get; set; }
public DateTime? ModifyTime { get; set; }
[SugarColumn(IsIgnore = true)]
public List<Region> ChildList { get; set; }
}
}
示例代码:
using SqlSugar;
using SqlSugarStart.DbModels;
using System;
using System.Collections.Generic;
using System.Diagnostics;
namespace SqlSugarStart
{
/// <summary>
/// 区域层级查询
/// </summary>
public static class Query1
{
public static void show()
{
try
{
SqlSugarClient sqlSugarClient = new SqlSugarClient(new ConnectionConfig
{
DbType = DbType.SqlServer,//要连接的数据库类型
ConnectionString = "server=.;uid=sa;pwd=123456;database=SqlSugarTest",//sqlsqver数据库链接字符串 (主库)
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
});
//第三个参数为0 表示 : parentid为 0 设置成根目录, 正常情况下最顶层的parentid不是0就是null
var allChilds1 = sqlSugarClient.Queryable<Region>().ToTree(it => it.ChildList, it => it.ParentId, 0);
foreach (var country in allChilds1)
{
Console.WriteLine($"一级名称:{country.Name}");
foreach (var province in country.ChildList)
{
Console.WriteLine($"二级名称:{province.Name}");
foreach (var city in province.ChildList)
{
Console.WriteLine($"三级名称:{city.Name}");
foreach (var area in city.ChildList)
{
Console.WriteLine($"四级名称:{area.Name}");
}
}
}
}
Console.WriteLine("*****************************************");
//获取主键为2的所有上级对象
var allChilds2 = sqlSugarClient.Queryable<Region>().ToParentList(it => it.ParentId, 2);
foreach (var item in allChilds2)
{
Console.WriteLine(item.Name);
}
Console.WriteLine("*****************************************");
//获取主键为2的所有下级对象
var allChilds3 = sqlSugarClient.Queryable<Region>().ToChildList(it => it.ParentId, 2);
foreach (var item in allChilds3)
{
Console.WriteLine(item.Name);
}
}
catch (Exception)
{
throw;
}
}
}
}
执行结果:
从后面两个查询结果可以看出,在查询上级信息和下级信息时,会同时带出当前层级的数据。
比如第二个查询,本意是查询ID=2(name=湖北省)的上级,最后查询的结果中也包含了‘湖北省’的记录。