Do you have a NUnit test case? 2009/4/3 Asif Tasleem <[email protected]>
> 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 > > > > -- Fabio Maulo --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
