10. 查询与分页进阶

后端手册SqlSugar查询分页大约 6 分钟约 1680 字

SqlSugarRepository<T> 自动选库 + 自动带全局过滤器，AsQueryable() 之后就是标准 SqlSugar 链式查询，ToPagedListAsync(page, pageSize) 返回 SqlSugarPagedList<T>。

1. 仓储从哪来、连哪个库

new SqlSugarRepository<T>()  构造函数里的选库顺序（Admin.NET.Core/SqlSugar/SqlSugarRepository.cs）
  1. 默认主库 ITenant.GetConnectionScope(SqlSugarConst.MainConfigId = "1300000000001")
  2. 实体贴 [Tenant(configId)] → GetConnectionScopeWithAttr<T>()          （最高优先级）
  3. 实体贴 [LogTable]         → 日志库 "1300000000002"（若已配置）
  4. 实体贴 [SysTable]         → 主库，直接返回
  5. 请求头 TenantId == 主库   → 主库
  6. Claims[TenantId] 非空且非主库 → SysTenantService.GetTenantDbConnectionScope(tenantId)

public class LeaveService : IDynamicApiController, ITransient
{
    private readonly SqlSugarRepository<Leave> _rep;   // 直接注入
    public LeaveService(SqlSugarRepository<Leave> rep) => _rep = rep;
}

常用仓储 API（继承 SimpleClient<T>）：

方法	说明
`AsQueryable()`	查询入口
`GetByIdAsync(id)` / `GetFirstAsync(exp)`	单条
`GetListAsync()` / `GetListAsync(exp)`	列表
`IsAnyAsync(exp)` / `CountAsync(exp)`	存在 / 计数
`InsertAsync(entity)` / `InsertReturnEntityAsync(entity)`	新增
`AsInsertable(entity).ExecuteReturnEntityAsync()`	新增并返回完整实体（含雪花 Id）
`AsUpdateable(entity).IgnoreColumns(true).ExecuteCommandAsync()`	更新（忽略 null 列）
`UpdateAsync(entity)` / `DeleteAsync(entity)` / `DeleteByIdAsync(id)`	改删
`Change<T>() / ChangeRepository<SqlSugarRepository<T>>()`	临时切到别的实体仓储
`CopyNew()`	复制一个不带过滤器/独立连接的实例
`Context`	拿到 `ISqlSugarClient`（原生 SQL、事务）

2. 条件拼接：WhereIF

[DisplayName("获取配置参数分页列表")]
public async Task<SqlSugarPagedList<SysConfig>> Page(PageConfigInput input)
{
    return await _sysConfigRep.AsQueryable()
        .WhereIF(!string.IsNullOrWhiteSpace(input.Name?.Trim()), u => u.Name.Contains(input.Name))
        .WhereIF(!string.IsNullOrWhiteSpace(input.Code?.Trim()), u => u.Code.Contains(input.Code))
        .WhereIF(!string.IsNullOrWhiteSpace(input.GroupCode?.Trim()), u => u.GroupCode.Equals(input.GroupCode))
        .OrderBuilder(input)
        .ToPagedListAsync(input.Page, input.PageSize);
}

WhereIF(bool, exp)：bool 为 false 时不拼接条件，避免手写 if。永远用它替代 if + Where，否则 IQueryable 变量类型会变，代码很难看。

3. 联表查询

// 用户分页：左连机构 + 职位（真实代码 Admin.NET.Core/Service/User/SysUserService.cs）
return await _sysUserRep.AsQueryable()
    .LeftJoin<SysOrg>((u, a) => u.OrgId == a.Id)
    .LeftJoin<SysPos>((u, a, b) => u.PosId == b.Id)
    .Where(u => u.AccountType != AccountTypeEnum.SuperAdmin)
    .WhereIF(orgList != null || extOrgUserIdList != null,
             u => orgList.Contains(u.OrgId) || extOrgUserIdList.Contains(u.Id))
    .WhereIF(!string.IsNullOrWhiteSpace(input.Keyword),
             (u, a, b) => u.Account.Contains(input.Keyword) || u.RealName.Contains(input.Keyword)
                       || b.Name.Contains(input.Keyword) || u.Phone.Contains(input.Keyword))
    .OrderBy(u => new { u.OrderNo, u.Id })
    .Select((u, a, b) => new UserOutput
    {
        OrgName = a.Name,
        PosName = b.Name,
        // 子查询把角色名拼成 "管理员,普通用户"
        RoleName = SqlFunc.Subqueryable<SysUserRole>()
            .LeftJoin<SysRole>((m, n) => m.RoleId == n.Id)
            .Where(m => m.UserId == u.Id)
            .SelectStringJoin((m, n) => n.Name, ","),
        // 子查询取单值
        DomainAccount = SqlFunc.Subqueryable<SysUserLdap>().Where(m => m.UserId == u.Id).Select(m => m.Account)
    }, true)   // ← 第二个参数 true = 自动填充主表同名字段
    .ToPagedListAsync(input.Page, input.PageSize);

要点：

Select((u, a, b) => new XxxOutput { ... }, true) 的 true 表示主表其余字段自动映射，只手写补充字段。
一对多转字符串用 SqlFunc.Subqueryable<X>().SelectStringJoin(x => x.Name, ",")，比内存 GroupBy 快得多。
多表 join 时 WhereIF 的 lambda 要带全部别名 (u, a, b) => ...。

导航属性方式（[Navigate] 已声明时）：

var list = await _rep.AsQueryable().Includes(u => u.Children).ToListAsync();

4. 子查询与 SqlFunc

// 存在性子查询
var q = _rep.AsQueryable()
    .Where(u => SqlFunc.Subqueryable<SysUserRole>().Where(m => m.UserId == u.Id && m.RoleId == 1).Any());

// 聚合子查询
.Select(u => new {
    Total = SqlFunc.Subqueryable<Leave>().Where(l => l.ApplicantId == u.Id).Count()
})

框架扩展的 SqlFunc（SqlSugarSetup.SetDbConfig 注册，只能用 Admin.NET.Core 里的静态方法）：

// 声明（真实代码 Admin.NET.Core/SqlSugar/SqlSugarSetup.cs）
public static string SqlFuncDecrypt<T>(T password) => throw new NotSupportedException("Can only be used in expressions");

// 用途：在 SQL 侧做 SM2 解密后比较，避免在内存里遍历
var user = await _rep.AsQueryable()
    .Where(u => SqlFuncDecrypt(u.Password) == input.PasswordOld)
    .FirstAsync();

SqlFuncDecrypt 只能出现在表达式里，直接调用会抛 NotSupportedException。

5. 动态排序 OrderBuilder

// Admin.NET.Core/Extension/RepositoryExtension.cs
public static ISugarQueryable<T> OrderBuilder<T>(this ISugarQueryable<T> queryable,
    BasePageInput pageInput, string prefix = "", string defaultSortField = "Id", bool descSort = true)

var list = await _rep.AsQueryable()
    .WhereIF(!string.IsNullOrWhiteSpace(input.Name), u => u.Name.Contains(input.Name))
    .OrderBuilder(input)                                   // 默认 Id Desc
    // .OrderBuilder(input, "u.", nameof(Leave.CreateTime), true)  // 联表时加前缀
    .ToPagedListAsync(input.Page, input.PageSize);

前端传 { page: 1, pageSize: 20, field: "CreateTime", order: "descending" } 即可排序。 OrderBuilder 内部做了两件事防注入：Regex.Replace(field, @"[\s;()\-'@=/%]", "") + EntityMaintenance.GetDbColumnName<T>(field)（字段不存在直接报错）。

6. 分页返回结构

public class SqlSugarPagedList<TEntity>
{
    public int Page { get; set; }
    public int PageSize { get; set; }
    public int Total { get; set; }
    public int TotalPages { get; set; }
    public IEnumerable<TEntity> Items { get; set; }
    public bool HasPrevPage { get; set; }
    public bool HasNextPage { get; set; }
    public dynamic TotalInfo { get; set; }
}

// 带 Select 投影的分页
var page = await _rep.AsQueryable()
    .Select(u => new XxxOutput { Id = u.Id, Name = u.Name })
    .ToPagedListAsync(input.Page, input.PageSize);

// 脱敏分页（字段贴了 [DataMask] 时）
var page2 = await _rep.AsQueryable().ToPagedListDataMaskAsync(input.Page, input.PageSize);

// 内存集合分页（已查出来的 List 再分页）
var page3 = list.ToPagedList(input.Page, input.PageSize);

7. 原生 SQL 与存储过程

// 查询列表
var regions = _rep.Context.Ado.SqlQuery<SysRegion>("SELECT * FROM sys_region WHERE level = 1");

// 动态对象
var rows = _rep.Context.Ado.SqlQuery<dynamic>(sql);

// DataTable
var dt = await _rep.Context.Ado.GetDataTableAsync(sql);

// 存储过程
var dt2 = await _rep.Context.Ado.UseStoredProcedure().GetDataTableAsync("sp_name", new SugarParameter[] { ... });

// 无返回执行
await _rep.Context.Ado.ExecuteCommandAsync("UPDATE sys_leave SET status = 2 WHERE id = @id",
    new SugarParameter("id", id));

8. 忽略过滤器：IgnoreTenant / ClearFilter / RunWithoutFilter

// 跨租户查账号是否重复（真实代码 SysUserService.AddUser）
var query = _sysUserRep.AsQueryable().ClearFilter<ITenantIdFilter>();
if (await query.AnyAsync(u => u.Account == input.Account)) throw Oops.Oh(ErrorCodeEnum.D1003);

// 语法糖，等价于 ClearFilter<ITenantIdFilter>()
var q2 = _sysUserRep.AsQueryable().IgnoreTenant();

// 清空全部过滤器（含软删除、机构、自定义）
var q3 = _sysUserRep.AsQueryable().ClearFilter();

// 更新/删除场景：临时清空并自动还原（RepositoryExtension.RunWithoutFilter）
await _rep.RunWithoutFilter(async () =>
{
    await _rep.AsUpdateable(entity).ExecuteCommandAsync();
});

RunWithoutFilter 内部用 db.CopyNew() + QueryFilter.ClearAndBackup() / Restore()，不会影响其他并发请求。

9. 大量 IN 查询：BulkListQuery

IN 里塞几千个 Id 会拖垮数据库，用 BulkListQuery 走临时表 + BulkCopy + InnerJoin：

// Admin.NET.Core/Extension/RepositoryExtension.cs
var list = await _rep.AsQueryable()
    .BulkListQuery<SysUser, long>((u, t) => u.Id == t.ColumnName, idList, stoppingToken);

内部：动态建临时表 → Fastest<T>().AS(table).BulkCopy(data) → InnerJoin → 查完 DropTable。

10. 步骤清单：写一个高性能分页查询

_rep.AsQueryable() 起手
所有可选条件用 .WhereIF(...)
需要展示关联字段时 LeftJoin + Select(..., true)，一对多文本用 SqlFunc.Subqueryable(...).SelectStringJoin(...)
排序用 .OrderBuilder(input)（前端可传 field/order），联表记得传 prefix
结尾 .ToPagedListAsync(input.Page, input.PageSize)，返回 SqlSugarPagedList<XxxOutput>
需要跨租户/跨删除态时加 .ClearFilter<ITenantIdFilter>() 或 .ClearFilter()
打开 Database.json 的 EnableConsoleSql: true 看生成的 SQL，确认没有全表扫描

⚠️ 注意事项 / 常见坑

全局过滤器是默认生效的：软删除、租户、机构（数据范围）都会自动加条件。查不到数据时先怀疑过滤器，用 EnableConsoleSql 打印 SQL。
超管（AccountTypeEnum.SuperAdmin）跳过所有过滤器，所以测试要用普通账号验证数据范围。
Select((u,a,b) => new XxxOutput{...}, true) 的 true 别漏，否则主表字段全是默认值。
OrderBuilder 的 Field 必须是实体属性名（走 GetDbColumnName 校验），传列名会抛异常。
SqlFunc.Subqueryable 里不能再套导航属性，只能写纯 SQL 可翻译的表达式。
SqlFuncDecrypt 只能在表达式中用，直接调用抛 NotSupportedException。
大批量 IN 用 BulkListQuery，别拼 Contains 到几千个元素。
long 主键超过 17 位返回给前端是字符串，前端比较时要当字符串处理。
ToPagedListAsync 内部会执行一次 COUNT，超大数据量表建议加索引或改用游标分页。
定时任务/事件总线里没有 App.User，租户过滤器不生效，需要手工 Where(u => u.TenantId == xxx)。

小结

记住这条链：AsQueryable() → WhereIF 拼条件 → LeftJoin + Select(..., true) 补字段 → Subqueryable 做聚合/拼接 → OrderBuilder(input) 排序 → ToPagedListAsync(Page, PageSize)。需要绕过隔离时用 ClearFilter<T>() / IgnoreTenant() / RunWithoutFilter()。