nhibernate: Class Mapping with no table

Question

I have an entity class that I use to represent the results of an sql-query. The mapping for the class shown below. Yet as far as I can tell nhiberate treats the mapping as if there is a real database table - when in fact there is not. In this case there is nothing in the database that represents this entity at all. I am using this to map a query through, but the same would be true of a view. Is there no way to indicate to nhibernate that there isn't a table represented by the mapping?

<class name="Models.UserTransaction"> <!-- Defaults table name same as Entity even though table doesn’t exist -->
  <id name="Id">
    <column name="Id" not-null="true" unique="true" />
    <generator class="native" />
  </id>
  <property name="TransType" />
  <property name="Date" />
  <property name="Amount" />
  <property name="Balance" />
</class>

This is the query I am mapping, which uses a user defined table. I couldn't get it working without having a mapping even though the example I copied appeared to.

  <sql-query name="UserTransactions">
    <query-param name="userId" type="string" />
    <return class="Models.UserTransaction" alias="userTx">
      <return-property name="TransType" column="TransType" />
      <return-property name="Id" column="Id" />
      <return-property name="Date" column="TransDate" />
      <return-property name="Amount" column="Amount" />
      <return-property name="Balance" column="Balance" />
    </return>
    <![CDATA[
      SELECT userTx.[Type] as TransType, userTx.[Id] as Id, userTx.[Date] as TransDate, userTx.[Amount] as Amount, userTx.[Balance] as Balance
      FROM dbo.User_AccountStatement(:userId) userTx
    ]]>
  </sql-query>

If you don't want to map your class to the database, then.. why are you mapping it? :) just remove the mapping completely, tell nHib to not map it, and that's it — J. Ed
As the question says I want to map it to the results of a query, like a report, so a one way (i.e. database to application) mapping. Just seems wrong for nhibernate to think treat it like a two way mapping when its not. — Dale K
Looking at another query issue, I thought once again about your problem. Are you in fact searching for some ResultTransformer for SQL query and DTO ? relation.to/Bloggers/… — jbl
@DaleBurrell: I'm running into this exact problem right now. It makes perfect sense to have a one way mapping. An ORM is an Object Relational Mapper. That doesn't imply that inserts, updates and deletes are supported. It's a mapping tool for relational data to object oriented data. I really wish NHibernate would allow read/select only mappings for query results. We need to load data from many other tables, and creating a view for generating a report is overkill for our scenario. — Greg Burghardt

Fran Fran · Accepted Answer · 2012-04-30T15:18:21

If you have a db view, you can use nhibernate to map to that, but if all you are doing is storing the projection fields of the query there doesn't need to be a map at all.

How are you querying this data?

if you are using the criteria API, you can use the resultstransformer to map the returned object array to your class. the types have to match between your class that the projection.

if you are using the linq provider you can project directly into your class. so you'd have something like this

from s in Session.Query where s.some-property== "some-value" select new your-type { some-property-on-your-type = s.some-property, some-other-property-on-your-type = s.some-other-property }

There is no need to write a mapping to the database since you aren't mapping to an object in the database.

nhibernate: Class Mapping with no table

2 Answers