Aah, yeah, good one! 😀 On Thu, May 26, 2016 at 2:46 PM, Oskar Berggren <[email protected]> wrote:
> 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. > -- 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.
