[SOLVED] How to define table aliases for joined tables in plain SQL queries?

How to define table aliases for joined tables in plain SQL queries?

I have an entity that is mapped with a <join> element like this:

<class name="Entity" table="Entites">
  <id name="Id">
    <generator class="guid" />
  </id>
  <property name="SomeProperty" />
  <join table="EntityData" optional="true">
    <key column="entity_id" />
    <property name="SomeDataProperty />
  </join>
</class>

and a native SQL query that should query this entity like this (the actual query is way more complex of course and uses Oracle features that are not available in other types of NHibernate queries):

var query = session.CreateSQLQuery(
  "select {entity.*} from Entities {entity}" +
  "left outer join EntityData data on {entity}.Id = data.entity_id"
);
query.AddEntity("entity", typeof(Entity));

But this doesn't work because NHibernate injects the join-properties with a wrong alias ("entity_1_").

I've also tried specifying the table alias as "{data}" but then NHibernate doesn't replace it (still contains the curly braces in the plain SQL).

Adding a join result to the query (query.AddJoin("data", "entity.EntityData")) doesn't work too. I've debugged this and NHibernate ignores it because at some point it checks if the property is a collection or entity (but it is a component, so neither of the if-conditions is true).

A workaround is to specify the join alias exaclty like NHibernate generates it (in my case "entity_1_"), then everything works.

Has anyone an idea how this can be solved correctly?

edit: I'm now having the same problem when querying an entity that is mapped with inheritance mapping. I can define an alias for the base entity, but the inherited entities are named entity_1_, entity_2_ and so on.

Solution

Seems like this is not possible, so I've now created a feature request for it: NH-2822