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 <erikholman...@gmail.com>: > 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 nhusers+unsubscr...@googlegroups.com. > To post to this group, send email to nhusers@googlegroups.com. > 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 nhusers+unsubscr...@googlegroups.com. To post to this group, send email to nhusers@googlegroups.com. Visit this group at https://groups.google.com/group/nhusers. For more options, visit https://groups.google.com/d/optout.
