SQL Suger百万条数据查询列表

底层：Repository

public class Repository<T> : IRepository<T> where T : class, new(){
private readonly ISqlSugarClient _db;

public Repository(ISqlSugarClient db)
{
    _db = db;
}

// 查询列表，支持条件和分页
public async Task<List<T>> GetQueryable(Expression<Func<T, bool>> filter = null, int? pageIndex = null, int? pageSize = null)
{
    try
    {
        var query = _db.Queryable<T>();

        // 应用过滤条件
        if (filter != null)
        {
            query = query.Where(filter);
        }

        // 如果提供了分页参数
        if (pageIndex.HasValue && pageSize.HasValue)
        {
            query = query.Skip(pageSize.Value * (pageIndex.Value - 1)).Take(pageSize.Value);
        }

        return await query.ToListAsync();
    }
    catch (Exception ex)
    {
        // 处理异常或记录日志
        // 可考虑使用日志库如 NLog 或 Serilog
        return new List<T>();  // 返回空列表而不是 null
    }
}
public async Task<(int Count, List<T> List)> CountAndToListAsync(Expression<Func<T, bool>> filter = null, int? pageIndex = null, int? pageSize = null)
{
    try
    {
        var query = _db.Queryable<T>(); // 用合适的方式获取 IQueryable<T>

        // 应用过滤条件
        if (filter != null)
        {
            query = query.Where(filter);
        }

        // 记录总数
        var count = await query.CountAsync();

        // 如果提供了分页参数
        if (pageIndex.HasValue && pageSize.HasValue)
        {
            query = query.Skip(pageSize.Value * (pageIndex.Value - 1))
                         .Take(pageSize.Value);
        }

        // 执行查询
        var list = await query.ToListAsync();

        return (count, list); // 返回记录数和列表
    }
    catch (Exception ex)
    {
        // 处理异常或记录日志
        // 记录日志代码
        return (0, new List<T>()); // 返回空列表和记录数为0
    }
}
}

实现：IRepository

public interface IRepository<T> where T : class, new()
{
    Task<(int Count, List<T> List)> CountAndToListAsync(Expression<Func<T, bool>> filter = null, int? pageIndex = null, int? pageSize = null); 
}

服务：

namespace HIS.VIP.Application.VIP
{
    [DynamicWebApi]
    [ApiExplorerSettings(GroupName = "v1")]
    public class VIPService : IDynamicWebApi
    {

        public readonly SqlSugarContext db;
        private readonly IRepository<SavingsManagement> Savingsrepository;
        private readonly IMapper mapper;
        private readonly ISqlSugarClient _db;
        public VIPService(SqlSugarContext context,IRepository<SavingsManagement> savingsrepository)
        {
            this.db = context;
            
            this.mapper = mapper;
            this._db = _db;
            this.Savingsrepository = savingsrepository;
            
        }
        
        
        /// <summary>
        /// 获取储值+条件查询+分页显示
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<ResultDTO<PageDTO<List<SavingsManagement>>>> GetSavingPageDepartments(int PageIndex, int PageSize, int? Member_Grade, string SearchText)
        {
            // 构建查询条件
            Expression<Func<SavingsManagement, bool>> filter = x => true;

            // 添加 Member_Grade 过滤条件
            if (Member_Grade.HasValue)
            {
                filter = filter.And(x => x.Member_Grade == Member_Grade.Value);
            }

            // 处理 SearchText
            if (!string.IsNullOrWhiteSpace(SearchText))
            {
                var searchParts = SearchText.Split('/');
                string namePart = searchParts.ElementAtOrDefault(0) ?? string.Empty; // 姓名
                string cardNumberPart = searchParts.ElementAtOrDefault(1) ?? string.Empty; // 卡号
                string phoneNumberPart = searchParts.ElementAtOrDefault(2) ?? string.Empty; // 电话号码

                if (!string.IsNullOrWhiteSpace(namePart))
                {
                    filter = filter.And(x => x.Member_Name.Contains(namePart));
                }
                if (!string.IsNullOrWhiteSpace(cardNumberPart))
                {
                    filter = filter.And(x => x.Card_Number.Contains(cardNumberPart));
                }
                if (!string.IsNullOrWhiteSpace(phoneNumberPart))
                {
                    filter = filter.And(x => x.Phone.Contains(phoneNumberPart));
                }
            }

            // 使用 GetQueryable 一次性查询所需数据和总记录数
            var (count, list) = await Savingsrepository.CountAndToListAsync(filter, PageIndex, PageSize);

            return new ResultDTO<PageDTO<List<SavingsManagement>>>
            {
                Code = 0,
                Message = "获取成功",
                Data = new PageDTO<List<SavingsManagement>>
                {
                    Total = count,
                    Page = (int)Math.Ceiling(count / (double)PageSize),
                    List = list
                }
            };
        }
        

    }
}