使用ServiceStack ORMLite在SQL Server 2008下实现SQL函数服务端分页
问题根因
当前实现触发内存分页的核心原因非常明确:
db.Select<T>(原生SQL)是立即执行的方法:调用时就会把传入的SQL完整发送到SQL Server,拿到全量结果集加载到应用内存后,才返回可枚举集合- 后续链式调用的
.Skip().Take()是LINQ to Objects的内存操作,完全不会参与SQL语句生成,因此数据库端永远不会收到分页逻辑,只能返回全部13.4万条数据,等应用内存做截断。
服务端分页的实现方式
OrmLite 原生支持将分页逻辑下推到SQL Server 2008执行,自动生成你期望的ROW_NUMBER()套层分页SQL。核心逻辑是不要在立即执行的查询上做内存操作,要利用OrmLite的延迟查询表达式构造完整的分页管道:
正确实现代码
public IEnumerable<Product> CallSqlFunction_dbo_Search_Products_View( int clientId, string environmentCode, int sessionId, int pageIndex = 0, int pageSize = 1000) { using var db = _dbConnectionFactory.Open(); // 构造延迟执行的查询表达式,将表值函数作为查询数据源 var query = db.From<Product>() // 关联表值函数,绑定参数 .From( $"dbo.Search_Products_View(@clientId, @envCode, @sessionId)", new { clientId, envCode = environmentCode, sessionId } ) // 必须指定分页依赖的明确排序规则,否则无法生成稳定的ROW_NUMBER分页 .OrderBy<Product>(x => x.ProductId) .ThenBy<Product>(x => x.ProductName); // 按需追加过滤逻辑,OrmLite会自动拼到分页子查询内部 // 例:匹配多状态的过滤条件 var validStatusList = new[] {"1", "2", "3"}; query.Where<Product>(x => validStatusList.Contains(x.ProductStatus.ToLower())); // 调用OrmLite分页API,自动触发SQL Server 2008的ROW_NUMBER分页下推 var pagedQuery = query.Page(pageIndex, pageSize); return db.Select(pagedQuery); }
生成的SQL逻辑说明
上述代码执行时,OrmLite检测到SQL Server 2008不支持OFFSET/FETCH语法,会自动生成和你预期结构完全一致的分页SQL:
- 内层子查询:对表值函数的结果应用过滤条件,加上
ROW_NUMBER() OVER (ORDER BY ProductId, ProductName)的行号计算 - 外层查询:根据传入的页索引、每页行数,用
ROW_NUMBER BETWEEN 起始值 AND 结束值过滤对应页的数据 - 全量逻辑下推到数据库执行,只会返回当页的1000条数据,不会拉取全量结果到应用内存。
常见注意事项
- 绝对不要对
db.Select立即返回的内存集合调用.Skip().Take(),这永远是内存操作,不会触发SQL下推 - 分页逻辑必须依赖明确的
OrderBy排序规则,没有排序的分页结果是不稳定的,OrmLite也无法生成合法的ROW_NUMBER分页语句 - 所有传入表值函数、过滤条件的参数都会自动被OrmLite打包成参数化查询,和原生
sp_executesql执行逻辑完全一致,无SQL注入风险 - 如果需要完全自定义原生SQL,可以手动调用
query.ToSelectStatement()拿到拼接好分页逻辑的最终SQL,再单独传参执行,适合需要做复杂SQL自定义的场景
内容的提问来源于stack exchange,提问作者Adrian Wright
相关产品推荐
相关产品推荐

