Hi all,

I have the following problem: I need to select an entity House from a view HOUSES_VIEW. However, the ID of HOUSE is not unique in that view. I would like to pre-select using e.g. SELECT DISTINCT ID, NAME FROM HOUSES_VIEW as value for the "table" attribute, instead of only the table name. Is this possible? I already tried, but nhibernate issues me an ADOException that it cannot be executed. I know, I could create simply another view and select from that one, but that should be avoided, if possible.

Are there other solutions? Is the "subselect" attribute the way to go? Below is an example.

unique in theory, but not working:
<class name="MyCode.House" mutable="false" table="(SELECT DISTINCT ID, NAME FROM HOUSES_VIEW)">

non-unique, original:
<class name="MyCode.House" mutable="false" table="HOUSES_VIEW">

Thanks in advance!

Best regards,
Martin

You can specifiy custom SQL statements for load/ubdateinsert/delete:

http://nhforge.org/doc/nh/en/index.html#querysql-cud

That's the only way you can do what you want with hibernate (except the view you already mentioned).

Hello,

Thanks a lot! It seems to be the way to go. However, I have a few issues implementing it:
Having:
<sql-query name="load" read-only="true">
<return class="MyCode.House, MyCode"></return>
<![CDATA[
select distinct ID, NAME FROM HOUSE
]]>
</sql-query>

and specified :
<loader query-ref="load"/>

it produces approximately the same SQL code, but specifying at the end ...FROM HOUSE instead of: ... FROM HOUSES_VIEW. Clearly, since HOUSE is not a table, it cannot be accessed and a NHibernateADOException is issued, specifying the table does not exist.

Any ideas?

Thanks a lot!
Martin

You have to use the view instead of the table: select distinct ID, NAME FROM HOUSE_VIEW.

There's one thing you have to remember when usin custom sql for loading. It only is used for session.Get or session.Load. HQL and criterias will still use the table that is specified in the table attribute of the class.

Hi,

Sorry, apparently I made a mistake in my previous post.

I did already use HOUSES_VIEW in the named sql query. When executing the query, it still won't work, issuing an SQL command using ... FROM House hous_0. An NHibernateADOException is issued, specifying the table does not exist.

Sorry for the mistake.

I still can't figure out what the problem is there. Do you have any more ideas?

Thanks a lot and best regards,
Martin

Can you post the complete mapping file ?

Sure! Here it is:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MyCode.House, MyCode" mutable="false" lazy="false" >
<id name="Id" column="ID" type="System.Int32"/>
<property name="Name" column="NAME" type="System.String" />
<loader query-ref="load"/>
</class>
<query name="GetHouses">
<![CDATA[
select h from House h
]]>
</query>
<sql-query name="load" read-only="true">
<return class="MyCode.House, MyCode"></return>
<![CDATA[
select distinct ID, NAME FROM HOUSES_VIEW
]]>
</sql-query>
</hibernate-mapping>

Try this:





What do you mean with query ? As I said, when you use HQL or a criteria, the custom SQL will NOT be used. If you need to be able to run flexible queries against the view, you have to map the view itself (or in your case define another view with unique rows).