Hello,

I would like to use a sql query to load an entity. I work with nhibernate 2.1.0 and a MySQL database.

I followed the instructions in the documentation (http://nhforge.org/
doc/nh/en/index.html#querysql-load)

But I get this error:
Table 'magento131.Shipping' doesn't exist

Here is my mapping file:

<class name="Shipping" lazy="false">
<id name="Id">
<generator class="assigned" />
</id>
<property name="Label" update="false" />
<loader query-ref="shipping"/>
</class>

<sql-query name="shipping">
<return alias="ship" class="Shipping" />
select distinct shipping_method as {ship.Id}, REPLACE
(shipping_method , '_', ' ') as {ship.Label}
from delivery_days
</sql-query>

What am I doing wrong?

Thank you in advance for your help.

mathmax

1. Make sure the mapping file is "Embedded Resource"
2. Use the fully qualified classname or specify this information on the mapping tag:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="sx.business.partner" assembly="sx.business">
3. You have no tablename on the class mapping. I suppose it's mandatory, because ICriteria and IQuery don't use the custom sql.
4. No clue .....

Mapping itself looks fine.

ok, I have to use this to get the data.

var data = _session.GetNamedQuery("shipping").List();

But why don't ICriteria, IQuery and NHibernate.Linq use the query instead of looking for a table since the tag "<loader query-ref="shipping"/>" says that the entity should be loaded via the query?

Just think of what a variety of queries you can construct with ICriteria and IQuery. There is no way to make sure that all these will work on top of a custom SQL. The custom sql is jut for session.Get and session.Load.

mmh... it's possible to use ICriteria and IQuery when an entity is loaded via a view that is stored on the database side. Thus, Nhibernate see this view as a Table. Why would not it be also possible when the view is defined in the mapping file ? The view could be seen be nh as a table too, couldn't it?

I'm not saying that it is possible in some or even a lot of cases. But there are also cases where it will probably not work, like with a stored procedure.