Databases
StackOverflow2013
Postsdbo
PONHibernate Left Outer Join Unrelated Entities
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate Left Outer Join Unrelated Entities
    primarykey
    data
    text
    <p>I have 2 entities Role &amp; Translation.<br> Role -> Role_ID, Code<br> Translation -> Code, Language, Name </p> <p>The idea is to say for a certain role, that it has English name, French name and so on.<br> For example:<br> A Role(1, 'Rol_001') can have the relations: Translation('Rol_001', 'English', '') &amp; Translation('Rol_001', 'French', ''). </p> <p>I would like to express the following SQL query in HQL:</p> <pre><code> select r.Role_ID, t.Name from Role r left outer join Translation t on r.Code = t.Code and t.Language = @lang; </code></pre> <p>In my mapping files I don't have any relation between the 2 entities but the following HQL query works as if it is inner join </p> <pre><code> IQuery query = session.CreateQuery("select new Lookup(r.Role, t.Name) from Role r, Translation t where r.Code = r.Code and t.Language = :language"); </code></pre> <p>If I change the HQL to left outer join, I get the Path expected for join exception. </p> <p>Can you help me with the following:<br> 1- Do I need to change my mapping files?<br> 2- If I can keep the mapping files as is, how write such a query in HQL?<br> 3- How does HQL really works? Why such a simple outer join query is not working? I must be missing something here!</p> <p><b>Edit:</b><br> Now I am using the following code based on the suggetion to use CreateSQL:</p> <pre><code> ISQLQuery query = session.CreateSQLQuery("select m.MedicineTypeID, t.Name, m.IsDeleted from MedicineType m left outer join Translation t on m.Code = t.Code and t.Language = :language"); query.SetString("language", language); IList rawLookup = query.List(); IList medicineTypesLookup = new List(rawLookup.Count); foreach (object[] lookup in rawLookup) { medicineTypesLookup.Add(new Lookup((int)lookup[0], (string)lookup[1], (bool)lookup[2])); } return medicineTypesLookup; </code></pre> <p>This is working however I want to use query.List() to get the result directly instead of converting it myself.<br> I tried to use <code>query.AddEntity(typeof(Lookup));</code> but I get the exception <code>NHibernate.MappingException: No persister for: DAL.Domain.Lookup</code>.<br> The Lookup is just a POCO and doesn't map to any database table. Its mapping file is simply <code>&lt;import class="Lookup" /&gt;</code></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsh_kamalh
    1. USsh_kamalh
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONHibernate Left Outer Join Unrelated Entities
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PONHibernate Left Outer Join Unrelated Entities
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHQL uses mapped relationships, not arbitrary join clauses. You can use SQL to achieve that.
      singulars
      1. PONHibernate Left Outer Join Unrelated Entities
      1. USDiego Mijelshon
    2. COThis is a SQL Server CE, can I execute SQL statement using NHibernate directly or shall I use SqlCeConnection and SqlCeCommand to ExecuteReader the sql statement and get the result?
      singulars
      1. PONHibernate Left Outer Join Unrelated Entities
      1. USsh_kamalh
    3. COI see that I can use session.CreateSQLQuery however the result is an Object[]. I am trying to find a way to get the result as IList<Lookup>.
      singulars
      1. PONHibernate Left Outer Join Unrelated Entities
      1. USsh_kamalh
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload