SqlSugar仓储架构通用查询/分页

SqlSugar是现在非常常用的ORM框架，在实际的项目中无论是DDD还是MVC一般都会使用仓储设计。这里展示的是通用查询和分页查询的方法

1、通用查询

/// <summary>
/// 通用的查询方法，支持单表查询和多表联查
/// </summary>
/// <typeparam name="TResult">返回的 DTO 类型</typeparam>
/// <param name="selectExpression">选择字段表达式</param>
/// <param name="joinExpression">联表表达式（可为空）</param>
/// <param name="whereExpressions">查询条件表达式集合（可为空）</param>
/// <param name="whereIfExpressions">动态条件表达式集合（可为空）</param>
/// <param name="orderExpressions">多排序表达式集合（可为空）</param>
/// <param name="tableName">查询主表的表名（可为空）</param>
/// <returns></returns>
public List<TResult> Query<TResult>(
    Expression<Func<TEntity, TResult>> selectExpression,
    Func<ISugarQueryable<TEntity>, ISugarQueryable<TEntity>>? joinExpression=null,
    List<Expression<Func<TEntity, bool>>>? whereExpressions = null,
    List<WhereIfExpression<TEntity>>? whereIfExpressions = null,
    List<OrderExpression<TEntity>>? orderExpressions = null,
    string? tableName = null)
{
    var query = string.IsNullOrEmpty(tableName)
        ? _db.Queryable<TEntity>()
        : _db.Queryable<TEntity>().AS(tableName);

    // 联表
    if (joinExpression != null)
    {
        query = joinExpression(query);
    }

    // 查询条件
    if (whereExpressions != null)
    {
        query = whereExpressions.Aggregate(
            query, 
            (current, whereExpression) => 
                current.Where(whereExpression));
    }

    if (whereIfExpressions != null)
    {
        query = whereIfExpressions.Aggregate(
            query, 
            (current, whereIfExpression) => 
                current.WhereIF(whereIfExpression.IsWhere, whereIfExpression.WhereIf));
    }
    
    // 排序条件
    if (orderExpressions != null)
    {
        query = orderExpressions.Aggregate(
            query, 
            (current, orderExpression) => 
                current.OrderBy(orderExpression.Order, orderExpression.OrderType));
    }

    // 选择字段
    return query.Select(selectExpression).ToList();
}

2、分页查询

/// <summary>
/// 通用的查询方法，支持单表查询和多表联查
/// </summary>
/// <typeparam name="TResult">返回的 DTO 类型</typeparam>
/// <param name="pageQueryResponse">分页查询返回对象</param>
/// <param name="selectExpression">选择字段表达式</param>
/// <param name="primaryKeyExpression">选择字段表达式</param>
/// <param name="joinExpression">联表表达式（可为空）</param>
/// <param name="whereExpressions">查询条件表达式集合（可为空）</param>
/// <param name="whereIfExpressions">动态条件表达式集合（可为空）</param>
/// <param name="orderExpressions">多排序表达式集合（可为空）</param>
/// <param name="tableName">查询主表的表名（可为空）</param>
/// <param name="pageIndex">查询第几页（可为空,默认第一页）</param>
/// <param name="pageSize">查询每页大小（可为空,默认一页10条记录）</param>
/// <returns></returns>
public PageQueryResponse<TResult> QueryPageing<TResult>(
    PageQueryResponse<TResult> pageQueryResponse,
    Expression<Func<TEntity, TResult>> selectExpression,
    Expression<Func<TEntity,TKey>> primaryKeyExpression,
    Func<ISugarQueryable<TEntity>, ISugarQueryable<TEntity>>? joinExpression=null,
    List<Expression<Func<TEntity, bool>>>? whereExpressions = null,
    List<WhereIfExpression<TEntity>>? whereIfExpressions = null,
    List<OrderExpression<TEntity>>? orderExpressions = null,
    string? tableName = null,
    int pageIndex = 0,
    int pageSize = 10)
{
    var query = string.IsNullOrEmpty(tableName)
        ? _db.Queryable<TEntity>()
        : _db.Queryable<TEntity>().AS(tableName);

    // 联表
    if (joinExpression != null)
    {
        query = joinExpression(query);
    }

    // 查询条件
    if (whereExpressions != null)
    {
        query = whereExpressions.Aggregate(
            query, 
            (current, whereExpression) => 
                current.Where(whereExpression));
    }

    if (whereIfExpressions != null)
    {
        query = whereIfExpressions.Aggregate(
            query, 
            (current, whereIfExpression) => 
                current.WhereIF(whereIfExpression.IsWhere, whereIfExpression.WhereIf));
    }
    
    // 排序条件
    if (orderExpressions != null)
    {
        query = orderExpressions.Aggregate(
            query, 
            (current, orderExpression) => 
                current.OrderBy(orderExpression.Order, orderExpression.OrderType));
    }

    
    var totalNumber = 0;
    var primaryKeys = query
        .Select(primaryKeyExpression)
        .ToPageList(pageQueryResponse.PageNumber,pageQueryResponse.PageSize,ref totalNumber);
    
    pageQueryResponse.TotalNumber = totalNumber;
    
    var pagedQuery =string.IsNullOrEmpty(tableName)
        ? _db.Queryable<TEntity>().Where(p => primaryKeys!.Contains(p.Id))
        : _db.Queryable<TEntity>().AS(tableName).Where(p => primaryKeys!.Contains(p.Id));
    
    // 联表（如果传入联表表达式）
    if (joinExpression != null)
    {
        pagedQuery = joinExpression(pagedQuery);
    }

    // 选择字段（如果传入选择字段表达式）
    pageQueryResponse.Data = pagedQuery.Select(selectExpression).ToList();
    
    // 选择字段
    return pageQueryResponse;
}