I don't understand what you mean... I mean, our code is pretty straight
forward and I don't think this differs much from the suggested way of
working:
var rowCount =
Session.CreateCriteria(typeof(VerwerkingsVerslagSamenvatting))
.Add(Restrictions.Eq("ServicecenterCode", servicecenterCode))
.SetProjection(Projections.RowCount())
.FutureValue<int>();
var results =
Session.CreateCriteria<VerwerkingsVerslagSamenvatting>()
.Add(Restrictions.Eq("ServicecenterCode", servicecenterCode))
.AddOrder(new Order(sortColumn, sortDirection.ToUpper() == "ASC"))
.SetFirstResult(pageNumber * pageSize)
.SetMaxResults(pageSize)
.Future<VerwerkingsVerslagSamenvatting>();
Please keep in mind that we're still using NH2.1....
On Thursday, May 26, 2016 at 11:20:04 AM UTC+2, Oskar Berggren wrote:
>
> Hmm... it sound like you are using paging while failing to specify a
> stable ordering. This is doomed to be unreliable. For stable paging you
> should apply an order clause that includes at least one unique column.
>
> /Oskar
> Den 25 maj 2016 1:39 em skrev "Gunnar Liljas" <[email protected]
> <javascript:>>:
>
>> I'm guessing, but I think you should.
>>
>> 1. Create a new dialect deriving from MsSql2008Dialect
>> 2. Override the GetLimitString method
>> 3. If offset is null, change it to "0"
>>
>>
>> https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Dialect/MsSql2005Dialect.cs
>>
>> 2016-05-25 10:22 GMT+02:00 Erik H <[email protected] <javascript:>>:
>>
>>> Hi,
>>>
>>> Something strange is going on with a paging implementation... We're
>>> using SQL Server 2008 (dialect: MsSql2008Dialect)
>>>
>>> We're using paging with SetFirstResult and SetMaxResults and it's
>>> working like it should.
>>>
>>> When we're adding an order things get weird.
>>>
>>> From what I understand to so far is that SetFirstResult(0) is doing
>>> *nothing
>>> extra* to the generated query. The Order criteria is added at the end
>>> of the query, like a normal order clause. With a number larger than 0 the
>>> query gets fundamentally different.
>>> I tried to trick the system, by feeding -1 but that didn't help ;-)
>>> Another 'hack' I tried is to add an SQL Expression where the ORDER clause
>>> is added by hand, but it automatically get's translated in a query like the
>>> 2nd
>>>
>>> *SetFirstResult(0) generates:*
>>> SELECT top 25 this_.VerwerkingsVerslagSamenvattingId as
>>> Verwerki1_140_0_, this_.ServicecenterCode as Servicec2_140_0_,
>>> this_.Bedrijfsnummer as AsapBedr3_140_0_, this_.InterfaceNaam as
>>> Interfac4_140_0_, this_.Bestandsnaam as Bestands5_140_0_,
>>> this_.TotaalAantalRegels as TotaalAa6_140_0_, this_.TotaalAantalGoed as
>>> TotaalAa7_140_0_, this_.TotaalAantalFout as TotaalAa8_140_0_,
>>> this_.TotaalAantalWaarschuwingen as TotaalAa9_140_0_, this_.Startdatum as
>>> Startdatum140_0_, this_.Einddatum as Einddatum140_0_ FROM
>>> [Database].[dbo].[VerwerkingsVerslagSamenvatting] this_ WHERE
>>> this_.ServicecenterCode = @p1 ORDER BY ServicecenterCode ASC;
>>> ;@p0 = 'SSC', @p1 = 'SSC'
>>>
>>> *SetFirstResult(25) generates:*
>>> SELECT TOP 25 Verwerki1_140_0_, Servicec2_140_0_, AsapBedr3_140_0_,
>>> Interfac4_140_0_, Bestands5_140_0_, TotaalAa6_140_0_, TotaalAa7_140_0_,
>>> TotaalAa8_140_0_, TotaalAa9_140_0_, Startdatum140_0_, Einddatum140_0_ FROM
>>> (SELECT this_.VerwerkingsVerslagSamenvattingId as Verwerki1_140_0_,
>>> this_.ServicecenterCode as Servicec2_140_0_, this_.AsapBedrijfsnummer as
>>> AsapBedr3_140_0_, this_.InterfaceNaam as Interfac4_140_0_,
>>> this_.Bestandsnaam as Bestands5_140_0_, this_.TotaalAantalRegels as
>>> TotaalAa6_140_0_, this_.TotaalAantalGoed as TotaalAa7_140_0_,
>>> this_.TotaalAantalFout as TotaalAa8_140_0_,
>>> this_.TotaalAantalWaarschuwingen as TotaalAa9_140_0_, this_.Startdatum as
>>> Startdatum140_0_, this_.Einddatum as Einddatum140_0_, ROW_NUMBER()
>>> OVER(ORDER BY ServicecenterCode) as __hibernate_sort_row FROM
>>> [Raet.Midoffice.Database].[dbo].[VerwerkingsVerslagSamenvatting] this_
>>> WHERE this_.ServicecenterCode = @p1) as query WHERE
>>> query.__hibernate_sort_row > 25 ORDER BY query.__hibernate_sort_row;;@p0 =
>>> 'SSC', @p1 = 'SSC'
>>>
>>> The second query gives other results than the first query and I actually
>>> don't know why. I assume that SQL Server applies another order strategy
>>> with the ROW_NUMBER function.
>>>
>>> Is there any workaround known so the order clause is exactly the same
>>> for every query? My impression is that the newer NHibernate versions are
>>> handling this correct. We're in the middle of upgrading several components
>>> (including NHibernate) but this is something that cannot wait for the
>>> upgrade.
>>>
>>> Any hints will be appreciated!
>>>
>>> Regards
>>> Erik
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "nhusers" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected] <javascript:>.
>>> To post to this group, send email to [email protected]
>>> <javascript:>.
>>> Visit this group at https://groups.google.com/group/nhusers.
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "nhusers" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected] <javascript:>.
>> To post to this group, send email to [email protected]
>> <javascript:>.
>> Visit this group at https://groups.google.com/group/nhusers.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
