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.
