What Oskar is saying is that ServicecenterCode is not unique enough to provide safe ordering. You should add something else, e.g. the primary key, as a secondary order.
2016-05-26 11:41 GMT+02:00 Erik H <[email protected]>: > 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]>: >> >>> 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]>: >>> >>>> 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]. >>>> 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. >>>> >>> >>> -- >>> 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. >>> >> -- > 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. > -- 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.
