We are having problems creating computed properties using the FORMULA attribute of the Property element.
Allow me to set up the scenario; We wish to have a computed property in our class that (obviously) does not map directly to a column in the SQL server table. The Property SIDE is computed from whether the quantity field is positive or negative (for our own reasons it is actually the inverse e.g., -1 for BUY, a Positive amount , and 1 for SELL a negative amount).
We wish to be able to search and return lists based upon this property.
We created a property in our mapping XML file using a formula; We tried many variations but it seems that NHibernate, when it parses the SQL string , wants to qualify all the keywords in the SQL with 'this' unless it is a simple string.
An example:
Code:
<property name="Side" formula="(select convert(smallint, (-1 * sign(O.quantity))) from dbo.tblorders o where o.orderid = this.Orderid)" />
When the application is run , the actual SQL sent to the server ends up as
Code:
(select convert(this.smallint, (-1 * sign(O.quantity))) from dbo.tblorders o where o.orderid = this.Orderid) as f0_11_
Similarly, if I attempt a CASE switch , all the words are qualified :
Code:
select 'OrderSide' = case when o.quantity >= 0 then -1 else 1 end from dbo.tblOrders O where o.orderid = Orderid)
ends up i the captured SQL as
Code:
(select ''OrderSide'' = this.case this.when o.quantity >= 0 this.then -1 this.else 1 this.end from dbo.tblOrders O where o.orderid = this.Orderid) as f0_11_
We have tested the formula attribute for a Property with simpler SQL strings and it does seem to work.
Please help a) Why are some of the strings have all words qualified and others not. b) Is there some way to stop this qualification of tables?
Kind Regards :roll:
Login
FAQ
Search