Exactly! To put it another way, there is absolutely no guarantee that the database will return the exact same ordering from one call to the next unless you have something unique as part of the ordering. Den 26 maj 2016 1:41 em skrev "Gunnar Liljas" <[email protected]>:
> 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. > -- 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.
