// 订单实体
public class Order
{
    public long Id { get; set; }
    public string OrderNo { get; set; }
    public decimal Amount { get; set; }
    public DateTime CreateTime { get; set; }
    public int Status { get; set; } // 0=待支付 1=已完成 2=已取消
    public string CustomerName { get; set; }
}

// DbContext
public class AppDbContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
}

// 订单查询条件
public class OrderQueryDto
{
    public string? OrderNo { get; set; }
    public DateTime? StartTime { get; set; }
    public DateTime? EndTime { get; set; }
    public int? Status { get; set; }
    public string? CustomerName { get; set; }
    
    // 分页参数
    public int PageIndex { get; set; } = 1;
    public int PageSize { get; set; } = 20;
}

•  AsNoTracking()：禁用跟踪，大幅提升查询速度，减少内存占用 
•  IQueryable 动态构建：只拼接条件，不执行 SQL，最后统一执行 
•  Skip/Take：数据库端分页，只查当前页数据 
•  ToListAsync()：异步执行，不阻塞线程，适配高并发
using Microsoft.EntityFrameworkCore;

public class OrderService
{
    private readonly AppDbContext _dbContext;
    public OrderService(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    /// <summary>
    /// 动态条件 + 高效分页查询
    /// </summary>
    public async Task<(List<Order> List, long TotalCount)> GetOrderPageAsync(OrderQueryDto query)
    {
        // 1. 基础查询：禁用跟踪 + 无锁查询（百万级必加）
        var queryable = _dbContext.Orders
            .AsNoTracking()
            .AsSplitQuery()  // 复杂查询拆分，避免笛卡尔积
            .AsQueryable();

        // 2. 动态构建条件（IQueryable 延迟执行，不触发查询）
        if (!string.IsNullOrWhiteSpace(query.OrderNo))
            queryable = queryable.Where(o => o.OrderNo.Contains(query.OrderNo));

        if (query.StartTime.HasValue)
            queryable = queryable.Where(o => o.CreateTime >= query.StartTime.Value);

        if (query.EndTime.HasValue)
            queryable = queryable.Where(o => o.CreateTime <= query.EndTime.Value);

        if (query.Status.HasValue)
            queryable = queryable.Where(o => o.Status == query.Status.Value);

        if (!string.IsNullOrWhiteSpace(query.CustomerName))
            queryable = queryable.Where(o => o.CustomerName.Contains(query.CustomerName));

        // 3. 获取总条数（仅 COUNT，高效）
        var totalCount = await queryable.LongCountAsync();

        // 4. 数据库端分页（核心：Skip/Take 生成 SQL 分页）
        var skip = (query.PageIndex - 1) * query.PageSize;
        var list = await queryable
            .OrderByDescending(o => o.CreateTime) // 必须排序，Skip/Take 要求
            .Skip(skip)
            .Take(query.PageSize)
            .ToListAsync();

        return (list, totalCount);
    }
}
•  不加载全表到内存：流式读取数据、流式写入 Excel 
•  NPOI 流式写入：SXSSF 模式，自动刷盘，内存占用极低 
•  分批次读取：每次只查 1000 条，循环写入，内存恒定
Install-Package NPOI
Install-Package NPOI.OpenXml4Net
Install-Package NPOI.OpenXmlFormats
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using System.IO;

/// <summary>
/// 百万级订单流式导出 Excel（无内存爆炸）
/// </summary>
public async Task<byte[]> ExportOrderExcelAsync(OrderQueryDto query)
{
    // 1. 复用动态查询条件（不加载全表）
    var queryable = _dbContext.Orders
        .AsNoTracking()
        .AsQueryable();

    // 动态条件（和分页查询完全一致）
    if (!string.IsNullOrWhiteSpace(query.OrderNo))
        queryable = queryable.Where(o => o.OrderNo.Contains(query.OrderNo));
    if (query.StartTime.HasValue)
        queryable = queryable.Where(o => o.CreateTime >= query.StartTime.Value);
    if (query.EndTime.HasValue)
        queryable = queryable.Where(o => o.CreateTime <= query.EndTime.Value);
    if (query.Status.HasValue)
        queryable = queryable.Where(o => o.Status == query.Status.Value);
    if (!string.IsNullOrWhiteSpace(query.CustomerName))
        queryable = queryable.Where(o => o.CustomerName.Contains(query.CustomerName));

    // 2. 排序（必须）
    queryable = queryable.OrderByDescending(o => o.Id);

    // 3. NPOI 流式工作簿（SXSSF 关键：低内存模式）
    var workbook = new SXSSFWorkbook(1000); // 每次保留1000行在内存
    var sheet = workbook.CreateSheet("订单数据");

    // 4. 创建表头
    var headerRow = sheet.CreateRow(0);
    headerRow.CreateCell(0).SetCellValue("订单ID");
    headerRow.CreateCell(1).SetCellValue("订单号");
    headerRow.CreateCell(2).SetCellValue("订单金额");
    headerRow.CreateCell(3).SetCellValue("创建时间");
    headerRow.CreateCell(4).SetCellValue("状态");
    headerRow.CreateCell(5).SetCellValue("客户姓名");

    var rowIndex = 1;
    var batchSize = 1000; // 每批次读取1000条（可调整）
    long skip = 0;

    // 5. 流式循环读取 + 写入（核心：永不加载全表）
    while (true)
    {
        // 每次只查 1000 条
        var batchData = await queryable
            .Skip((int)skip)
            .Take(batchSize)
            .ToListAsync();

        if (batchData.Count == 0) break; // 无数据退出

        // 写入当前批次
        foreach (var order in batchData)
        {
            var row = sheet.CreateRow(rowIndex++);
            row.CreateCell(0).SetCellValue(order.Id);
            row.CreateCell(1).SetCellValue(order.OrderNo);
            row.CreateCell(2).SetCellValue((double)order.Amount);
            row.CreateCell(3).SetCellValue(order.CreateTime.ToString("yyyy-MM-dd HH:mm:ss"));
            row.CreateCell(4).SetCellValue(order.Status switch { 0 => "待支付", 1 => "已完成", 2 => "已取消" });
            row.CreateCell(5).SetCellValue(order.CustomerName);
        }

        skip += batchSize;
    }

    // 6. 流式写入到字节数组（不占用大内存）
    using var ms = new MemoryStream();
    workbook.Write(ms, true); // 流式写入
    workbook.Close();
    ms.Position = 0;

    return ms.ToArray();
}

最终 API 调用（Web API）
[ApiController]
[Route("api/orders")]
public class OrderController : ControllerBase
{
    private readonly OrderService _orderService;
    public OrderController(OrderService orderService)
    {
        _orderService = orderService;
    }

    // 分页查询
    [HttpGet("page")]
    public async Task<IActionResult> GetPage([FromQuery] OrderQueryDto query)
    {
        var (list, total) = await _orderService.GetOrderPageAsync(query);
        return Ok(new { List = list, Total = total });
    }

    // 流式导出
    [HttpGet("export")]
    public async Task<IActionResult> Export([FromQuery] OrderQueryDto query)
    {
        var bytes = await _orderService.ExportOrderExcelAsync(query);
        return File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
            $"订单导出_{DateTime.Now:yyyyMMddHHmmss}.xlsx");
    }
}

数据库索引（性能提升 10~100 倍）

CREATE INDEX IX_Orders_CreateTime ON Orders(CreateTime);
CREATE INDEX IX_Orders_Status ON Orders(Status);
CREATE INDEX IX_Orders_OrderNo ON Orders(OrderNo);