Good point. 2016-05-26 11:19 GMT+02:00 Oskar Berggren <[email protected]>:
> 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.
