首页 / 知识

Linq到NHibernate多个OrderBy调用

2023-04-15 05:38:00

Linq to NHibernate multiple OrderBy calls

我在Linq to NHibernate查询中无法通过多个字段进行排序。是否有人知道可能出什么问题或是否有解决方法？

码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

IQueryable<AgendaItem> items = _agendaRepository.GetAgendaItems(location)
.Where(item => item.Minutes.Contains(query) || item.Description.Contains(query));

int total = items.Count();

var results = items
.OrderBy(item => item.Agenda.Date)
.ThenBy(item => item.OutcomeType)
.ThenBy(item => item.OutcomeNumber)
.Skip((page - 1)*pageSize)
.Take(pageSize)
.ToArray();

return new SearchResult(query, total, results);

我尝试用多个OrderBy调用替换ThenBy。结果相同。如果我注释掉两个ThenBy调用，则该方法效果很好。

我收到的错误：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

[SqlException (0x80131904): Invalid column name '__hibernate_sort_expr_0____hibernate_sort_expr_1__'.
Invalid column name '__hibernate_sort_expr_0____hibernate_sort_expr_1__'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392

[ADOException: could not execute query
[ SELECT this_.Id as Id5_2_, this_.AgendaId as AgendaId5_2_, this_.Description as Descript3_5_2_, this_.OutcomeType as OutcomeT4_5_2_, this_.OutcomeNumber as OutcomeN5_5_2_, this_.Minutes as Minutes5_2_, agenda1_.Id as Id2_0_, agenda1_.LocationId as LocationId2_0_, agenda1_.Date as Date2_0_, location2_.Id as Id7_1_, location2_.Name as Name7_1_ FROM AgendaItem this_ left outer join Agenda agenda1_ on this_.AgendaId=agenda1_.Id left outer join Location location2_ on agenda1_.LocationId=location2_.Id WHERE location2_.Id = ? and (this_.Minutes like ? or this_.Description like ?) ORDER BY agenda1_.Date asc, this_.OutcomeType asc, this_.OutcomeNumber asc ]
Positional parameters: #0>1 #0>%Core% #0>%Core%
[SQL: SELECT this_.Id as Id5_2_, this_.AgendaId as AgendaId5_2_, this_.Description as Descript3_5_2_, this_.OutcomeType as OutcomeT4_5_2_, this_.OutcomeNumber as OutcomeN5_5_2_, this_.Minutes as Minutes5_2_, agenda1_.Id as Id2_0_, agenda1_.LocationId as LocationId2_0_, agenda1_.Date as Date2_0_, location2_.Id as Id7_1_, location2_.Name as Name7_1_ FROM AgendaItem this_ left outer join Agenda agenda1_ on this_.AgendaId=agenda1_.Id left outer join Location location2_ on agenda1_.LocationId=location2_.Id WHERE location2_.Id = ? and (this_.Minutes like ? or this_.Description like ?) ORDER BY agenda1_.Date asc, this_.OutcomeType asc, this_.OutcomeNumber asc]]
NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +258
NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) +18
NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) +87
NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +342
NHibernate.Impl.CriteriaImpl.List(IList results) +41
NHibernate.Impl.CriteriaImpl.List() +35
NHibernate.Linq.CriteriaResultReader`1.List() in C:\home\dev\tools
Hibernate
HibernateContribSrc\src
Hibernate.Linq\src
Hibernate.Linq\CriteriaResultReader.cs:22
NHibernate.Linq.d__0.MoveNext() in C:\home\dev\tools
Hibernate
HibernateContribSrc\src
Hibernate.Linq\src
Hibernate.Linq\CriteriaResultReader.cs:27

在我看来，这就像Linq到NHybernate的错误。一种可能的解决方法是在排序之前转换为数组。一个潜在的大弊端是您不能在枚举之前使用Skip()和Take()来限制结果，因此这可能对您来说还不够。

1
2
3
4
5
6
7

var results = items
.ToArray()
.OrderBy(item => item.Agenda.Date)
.ThenBy(item => item.OutcomeType)
.ThenBy(item => item.OutcomeNumber)
.Skip((page - 1)*pageSize)
.Take(pageSize)

如果结果集相对较小，则将其首先转换为数组可能是可接受的解决方案。但是，如果您想让SQL Server来完成这项工作，则最好采用以下方式：

1
2
3
4
5
6
7

var results = items
.OrderBy(item => item.Agenda.Date).Asc
.ThenBy(item => item.OutcomeType).Asc
.ThenBy(item => item.OutcomeNumber).Asc
.Skip((page - 1)*pageSize)
.Take(pageSize)
.ToArray();

每当在Linq的NHibernate方言中使用OrderBy或OrderByAlias方法时，都应始终添加Asc或Desc修饰符。

尽管我认为这不会有所作为，但是如果您这样执行linq会发生什么：

(从i在按i.prop1，i.prop2，i.prop3排序的项目中).Skip(...)。Take(...)。ToArray();

调用解决方法排序字段