Please ignore columns y4_ to onward in first query. I removed them to reduce query test size.
On Fri, Apr 3, 2009 at 5:57 PM, Asif Tasleem <[email protected]> wrote: > Hi All, > > I was using NHibernate 2.0.1.GA release. I recently switched to > 2.1.0.Alpha2 release. > One of my criteria is fetching TOP 10 DISTINCT rows. It was working > fine in NH 2.0.1.GA. But in NH 2.1.0.Alpha2 it is not returning > Distinct rows. When I observed the underlying SQL generated by this > criteria it is entirely different in latest build and it is a cause of > this error. > > Following is the resulted query in 2.0.1.GA: > SELECT TOP 5 y0_, > y1_, > y2_, > y3_, > FROM (SELECT ROW_NUMBER() > OVER(ORDER BY y14_ DESC) as row, > query.y0_, > query.y1_, > query.y2_, > query.y3_, > FROM (SELECT distinct this_.ID as y0_, > this_.VersionNumber as y1_, > this_.TotalAmount as y2_, > (select SUM(a.Amount) > from TableA a > where a.ID= this_.ID > And a.Flag= 'True') as y3_, > > FROM dbo.TableB this_ > inner join dbo.TableA t1_ > on this_.ID = t1_.TransactionID > inner join dbo.TableC c2_ > on t1_.D = c2_.D > WHERE t1_.Flag = 1 /* @p0 */ > and c2_.CNumber = '5859' /* @p1 */) query) > page > WHERE page.row > 0 > ORDER BY y2_ DESC > > > While the same Criteria is generating following query in 2.1.0.Alpha2 > release: > > SELECT TOP 10 y0_, > y1_, > y2_, > y3_, > FROM (SELECT distinct this_.ID as y0_, > this_.VersionNumber as > y1_, > this_.TotalAmount as > y2_, > (select SUM(a.Amount) > from TableA a > where a.ID = this_.ID > And a.Flag= 'True') as y3_, > ROW_NUMBER() > OVER(ORDER BY this_.TotalAmount DESC) as > __hibernate_sort_row > FROM dbo.TableB this_ > inner join dbo.TableA t1_ > on this_.ID = t1_.ID > inner join dbo.TableC c2 > on t1_.D = c2.ID > WHERE t1_.Flag = 1 /* @p0 */ > and c2_.CNumber = '5859' /* @p1 */) as query > WHERE query.__hibernate_sort_row > 0 > ORDER BY query.__hibernate_sort_row > > Is it a bug got into NHibernate 2.1.0Alpha2 release? Am I missing some > thing in following Criteria API code: > > public IList<TableB> MyMethod(string number, int firstResult, > int maxResults, ref int count) > { > > ProjectionList proj = Projections.ProjectionList() > .Add(Projections.Alias(Projections.Property("Id"), > "Id")) > .Add(Projections.Alias(Projections.Property > ("VersionNumber"), "VersionNumber")) > .Add(Projections.Alias(Projections.Property > ("TotalAmount"), "TotalAmount")); > .Add(Projections.Alias(Projections.Property("Tax"), > "Tax")); > > ICriteria criteria = Session.CreateCriteria(typeof > (Transaction)).SetProjection(Projections.Distinct(proj)); > ICriteria txndetailCriteria = criteria.CreateCriteria > ("TableA"); > txndetailCriteria.Add(Expression.Eq("Flag", true)); > > txndetailCriteria.CreateCriteria("TableC").Add > (Expression.Eq("CNumber", number)); > criteria.AddOrder(Order.Desc("TotalAmount")); > criteria.SetResultTransformer(new > NHibernate.Transform.AliasToBeanResultTransformer(typeof(TableB))); > > > criteria.SetFirstResult(firstResult); > criteria.SetMaxResults(maxResults); > IList<TableB> result = criteria.List<TableB>(); > return result; > } > > Regards, > > Asif Tasleem > > > > -- Regards, Asif Tasleem --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
