Hibernate version:
2.1.0 Alpha 1
Name and version of the database you are using:
Sql Server 2005
I cant figure out how to use formulas in a criteria. This is a subset of what I am trying to do. I need to get a subquery as a property of a parent query. Then on the server I need to calculate a value and sort by that value. Finally, I will only return 20 rows.
Code:
// get the cash balance
DetachedCriteria dc = DetachedCriteria.For(typeof(Account), "a")
.CreateAlias("Holdings", "holdings")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Sum("holdings.Balance")))
.Add(Expression.EqProperty("b.ID", "a.ID"));
ICriteria cashPercent = session.CreateCriteria(typeof(Account), "b")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("ID"))
.Add(Projections.Property("Balance"))
.Add(Projections.Alias(Projections.SubQuery(dc), "Cash"))
.Add(Projections.SqlProjection("Cash / Balance As CashPercent",
new string[] { "CashPercent" },
new NHibernate.Type.IType[] { NHibernateUtil.Decimal })))
.AddOrder(Order.Asc("CashPercent"));
IList l = cashPercent.List();
The problem here is that the correlated subquery that I alias as CashPercent is not getting aliased. As it sits I get an exception:
Quote:
"could not resolve property: CashPercent of: XXX.Account"
If I remove the order statement I get a SQL error
Quote:
"Invalid column name 'Cash'."
Here is the SQL that is generated, you can see that y2_ is not being called "Cash"
Code:
SELECT this_.AccountID as y0_, this_.Balance as y1_,
(
SELECT sum(holdings1_.mktvalue) as y0_
FROM Account this_0_
inner join holdings holdings1_ on this_0_.AccountID=holdings1_.AccountID
WHERE this_.AccountID = this_0_.AccountID
) as y2_,
Cash / Balance As CashPercent
FROM Account this_
Is there some way that I need to set the aliases I use here. Both the aliases CashPercent and Cash seem to be getting ignored.
Login
FAQ
Search
