分表分页查询时，如果有 orderby，会产生错误的 sql 返回
taoran 发布于2周前
分表+分页查询时，如果有 orderby，第一个查总数的sql就报错了。
报错信息：
Microsoft.Data.SqlClient.SqlException (0x80131904): 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
关键字 'UNION' 附近有语法错误。
关键字 'UNION' 附近有语法错误。
关键字 'UNION' 附近有语法错误。
关键字 'UNION' 附近有语法错误。
关键字 'UNION' 附近有语法错误。
“)”附近有语法错误。
产生的SQL：
SELECT COUNT
( 1 )
FROM
(
SELECT
[Id] AS [Id],
[UserId] AS [UserId],
[OperateTime] AS [OperateTime],
[Account] AS [Account],
[UserName] AS [UserName],
[ClassName] AS [ClassName],
[ActionName] AS [ActionName],
[HttpMethod] AS [HttpMethod],
[Url] AS [Url],
[Parameter] AS [Parameter],
[Result] AS [Result],
[Status] AS [Status],
[IP] AS [IP],
[Location] AS [Location],
[UserAgent] AS [UserAgent],
[Elapsed] AS [Elapsed],
[Level] AS [Level],
[EventId] AS [EventId],
[ThreadId] AS [ThreadId],
[TraceId] AS [TraceId],
[CreatedBy] AS [CreatedBy],
[CreatedTime] AS [CreatedTime],
[OrgId] AS [OrgId],
[ActionLabel] AS [ActionLabel]
FROM
(
SELECT
[Id],
[UserId],
[Account],
[UserName],
[ClassName],
[ActionName],
[ActionLabel],
[HttpMethod],
[Url],
[Parameter],
[Result],
[Exception],
[Validation],
[Status],
[IP],
[Location],
[UserAgent],
[Elapsed],
[OperateTime],
[Level],
[EventId],
[ThreadId],
[TraceId],
[Message],
[CreatedBy],
[CreatedTime],
[OrgId]
FROM
[T_L_LogInterface_20240901]
WHERE
( [OperateTime] \ u003E = @ConditOperateTime10000UnionAll1 )
AND [CreatedTime] \ u003E =@spBeginTimeUnionAll1
AND [CreatedTime] \ u003C = @spEndTimeUnionAll1
ORDER BY
Id DESC
) unionTable
) CountTable
热忱回答（5）
-
fate sta VIP0
2周前提供很显了，SQL错误
0 回复 -
taoran VIP0
2周前
@fate sta：
不好意思，我的表述可能不太清晰，我是说，加上 .OrderBy 后 Sugar 会生成错误的 SQL
db.Queryable<Log>()
.OrderBy($"{fieldToSort} {page.SortOrder}") // 加上 OrderBy 就会生成错误的 SQL
.SplitTable(DateTime.Now.AddDays(-30), DateTime.Now)
.ToOffsetPageAsync(pageIndex, pageSize, total);
0 回复 -
fate sta VIP0
2周前
orderby必须写在splitable后面
0 回复 -
taoran VIP0
2周前
@fate sta：前后我都试过，最终生成出的 sql 都是错的，写 splitable 前面会有更多的错误，写splitable后面错误会少很多，但还是有错误
0 回复 -
fate sta VIP0
2周前
https://www.donet5.com/Home/Doc?typeId=2366
还有疑问按模版提供DMEO
0 回复