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_,
y4_,
y5_,
y6_,
y7_,
y8_,
y9_,
y10_,
y11_,
y12_,
y13_,
y14_
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
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
