NHibernate - Select query in Formula attribute of Property element is not working....

Question

I am new to NHibernate. I am using following property elements in my hbm file...

<property name="CountryId" length="4" />
<property name="CountryForCustomer"
              formula="(SELECT *
                            FROM SystemCountry  
                            WHERE SystemCountry.CountryId = CountryId)" />

Here I am trying to get Country details from the CountryId that I am having in an other table.

Property "CountryForCustomer" is of custom type "SystemCountry".

But this query in formula is not working.. So someone please help me solving this issue.

Thanks in Advance....

Answer 1

Should it not be

SELECT SystemCountry.Country FROM SystemCountry WHERE...

as the * would return multiple columns from the query

edit If you want to return the whole object back then the property element is not your friend here. What you want to do is look at the many-to-one element, note that this is just one way to do this.

<many-to-one name="CmsTemplate" column="TemplateId" ..

e.g. here or here