Hi!

I ´ve got 3 tables: Site, Hole and Status.

And also the 3 nhb.xml files for each one.


Want perform the folowing query:


Select Site.Description, Satus. Description, Hole.ID

WHERE Site.SiteId = Hole.SiteId and Status.StatusId = Hole.StatusId.


How can i make a inner join using HQL?


Regards,

Hi welennAraujo!

It will be something like this:

select h.id, h.Site.Description, h.Site.Status.Description from Hole h

I assume that you have properly mapped associations between all your classes (and that they all are many-to-one in this case).

But be careful - in such a syntax NHibernate will really do inner join (just like in your SQL example), that may be inappropriate for some cases (for example if you have NULL reference from Site to Status, you'll get empty result).

You have to read corresponding part of documentation (HQL queries) more carefully - it is really powerful thing, but it is a bit different than SQL...

Hi IgorK!

Thanks for the reply, but it doesn't work.

I used the MyGeneration software to provide the hbm and cs files.
But I don't know if its has been properly generated.

The database is the Oracle 10g.

Indeed i use that simple example to help you to understabd what i need.

I'll explain the problem better:

I ´ve got the tables Sa2pocot, Salminat and Sagcdstt.

Their aliases are Poco, Mina and Status.

I ´ve got the hbm.xml and cs files:

Sa2pocot:

hbm.xml:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MapeamentoOR.Sa2pocot,MapeamentoOR" table="SA2POCOT">
<id name="Sa2codpc" column="SA2CODPC" type="String">
<generator class="assigned"/>
</id>
<many-to-one name="Salidemn" column="SALIDEMN" class="MapeamentoOR.Salminat,MapeamentoOR" />
<many-to-one name="Sagidest" column="SAGIDEST" class="MapeamentoOR.Sagcdstt,MapeamentoOR" />
<property column="SA2DATIN" type="DateTime" name="Sa2datin" />
<property column="SA2NATPC" type="String" name="Sa2natpc" length="20" />
<property column="SA2USOAG" type="String" name="Sa2usoag" length="50" />
<property column="SA2VALCT" type="Decimal" name="Sa2valct" />
<property column="SA2BCHID" type="String" name="Sa2bchid" length="50" />
<property column="SA2SITPC" type="String" name="Sa2sitpc" length="50" />
<property column="SA2PROFU" type="Decimal" name="Sa2profu" />
<property column="SA2EMPPR" type="String" name="Sa2emppr" length="50" />
<property column="SA2METPR" type="String" name="Sa2metpr" length="50" />
<property column="SA2DIAPC" type="Decimal" name="Sa2diapc" />
<property column="SA2EQPUT" type="String" name="Sa2eqput" length="50" />
<property column="SA2GERES" type="String" name="Sa2geres" length="50" />
<property column="SA2RESSO" type="String" name="Sa2resso" length="50" />
<property column="SA2AQUIF" type="String" name="Sa2aquif" length="20" />
<property column="SA2CONDP" type="String" name="Sa2condp" length="50" />
<property column="SA2DATPR" type="DateTime" name="Sa2datpr" />
<property column="SA2DATPF" type="DateTime" name="Sa2datpf" />
<property column="SA2DATDV" type="DateTime" name="Sa2datdv" />
<property column="SA2OBSPC" type="String" name="Sa2obspc" length="150" />

</class>
</hibernate-mapping>




Sa2pocot cs:
using System;

namespace MapeamentoOR
{
/// <summary>
/// Generated by MyGeneration using the NHibernate Object Mapping adapted by Gustavo
/// </summary>
[Serializable]
public class Sa2pocot
{
#region Private Members
private bool _isChanged;
private bool _isDeleted;
private string _sa2codpc;
private Salminat _salidemn;
private Sagcdstt _sagidest;
private DateTime _sa2datin;
private string _sa2natpc;
private string _sa2usoag;
private decimal? _sa2valct;
private string _sa2bchid;
private string _sa2sitpc;
private decimal? _sa2profu;
private string _sa2emppr;
private string _sa2metpr;
private decimal? _sa2diapc;
private string _sa2eqput;
private string _sa2geres;
private string _sa2resso;
private string _sa2aquif;
private string _sa2condp;
private DateTime _sa2datpr;
private DateTime _sa2datpf;
private DateTime _sa2datdv;
private string _sa2obspc;
#endregion

#region Default ( Empty ) Class Constuctor
/// <summary>
/// default constructor
/// </summary>
public Sa2pocot()
{
_sa2codpc = null;
_salidemn = new Salminat();
_sagidest = new Sagcdstt();
_sa2datin = DateTime.MinValue;
_sa2natpc = null;
_sa2usoag = null;
_sa2valct = null;
_sa2bchid = null;
_sa2sitpc = null;
_sa2profu = null;
_sa2emppr = null;
_sa2metpr = null;
_sa2diapc = null;
_sa2eqput = null;
_sa2geres = null;
_sa2resso = null;
_sa2aquif = null;
_sa2condp = null;
_sa2datpr = DateTime.MinValue;
_sa2datpf = DateTime.MinValue;
_sa2datdv = DateTime.MinValue;
_sa2obspc = null;
}
#endregion // End of Default ( Empty ) Class Constuctor

#region Public Properties

/// <summary>
///
/// </summary>
public virtual string Sa2codpc
{
get { return _sa2codpc; }
set
{
if ( value != null)
if( value.Length > 10)
throw new ArgumentOutOfRangeException("Invalid value for Sa2codpc", value, value.ToString());

_isChanged |= (_sa2codpc != value); _sa2codpc = value;
}
}

/// <summary>
///
/// </summary>
public virtual Salminat Salidemn
{
get { return _salidemn; }
set { _isChanged |= (_salidemn != value); _salidemn = value; }
}

/// <summary>
///
/// </summary>
public virtual Sagcdstt Sagidest
{
get { return _sagidest; }
set { _isChanged |= (_sagidest != value); _sagidest = value; }
}

/// <summary>
///
/// </summary>
public virtual DateTime Sa2datin
{
get { return _sa2datin; }
set { _isChanged |= (_sa2datin != value); _sa2datin = value; }
}

/// <summary>
///
/// </summary>
public virtual string Sa2natpc
{
get { return _sa2natpc; }
set
{
if ( value != null)
if( value.Length > 20)
throw new ArgumentOutOfRangeException("Invalid value for Sa2natpc", value, value.ToString());

_isChanged |= (_sa2natpc != value); _sa2natpc = value;
}
}

/// <summary>
///
/// </summary>
public virtual string Sa2usoag
{
get { return _sa2usoag; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2usoag", value, value.ToString());

_isChanged |= (_sa2usoag != value); _sa2usoag = value;
}
}

/// <summary>
///
/// </summary>
public virtual decimal? Sa2valct
{
get { return _sa2valct; }
set { _isChanged |= (_sa2valct != value); _sa2valct = value; }
}

/// <summary>
///
/// </summary>
public virtual string Sa2bchid
{
get { return _sa2bchid; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2bchid", value, value.ToString());

_isChanged |= (_sa2bchid != value); _sa2bchid = value;
}
}

/// <summary>
///
/// </summary>
public virtual string Sa2sitpc
{
get { return _sa2sitpc; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2sitpc", value, value.ToString());

_isChanged |= (_sa2sitpc != value); _sa2sitpc = value;
}
}

/// <summary>
///
/// </summary>
public virtual decimal? Sa2profu
{
get { return _sa2profu; }
set { _isChanged |= (_sa2profu != value); _sa2profu = value; }
}

/// <summary>
///
/// </summary>
public virtual string Sa2emppr
{
get { return _sa2emppr; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2emppr", value, value.ToString());

_isChanged |= (_sa2emppr != value); _sa2emppr = value;
}
}

/// <summary>
///
/// </summary>
public virtual string Sa2metpr
{
get { return _sa2metpr; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2metpr", value, value.ToString());

_isChanged |= (_sa2metpr != value); _sa2metpr = value;
}
}

/// <summary>
///
/// </summary>
public virtual decimal? Sa2diapc
{
get { return _sa2diapc; }
set { _isChanged |= (_sa2diapc != value); _sa2diapc = value; }
}

/// <summary>
///
/// </summary>
public virtual string Sa2eqput
{
get { return _sa2eqput; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2eqput", value, value.ToString());

_isChanged |= (_sa2eqput != value); _sa2eqput = value;
}
}

/// <summary>
///
/// </summary>
public virtual string Sa2geres
{
get { return _sa2geres; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2geres", value, value.ToString());

_isChanged |= (_sa2geres != value); _sa2geres = value;
}
}

/// <summary>
///
/// </summary>
public virtual string Sa2resso
{
get { return _sa2resso; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2resso", value, value.ToString());

_isChanged |= (_sa2resso != value); _sa2resso = value;
}
}

/// <summary>
///
/// </summary>
public virtual string Sa2aquif
{
get { return _sa2aquif; }
set
{
if ( value != null)
if( value.Length > 20)
throw new ArgumentOutOfRangeException("Invalid value for Sa2aquif", value, value.ToString());

_isChanged |= (_sa2aquif != value); _sa2aquif = value;
}
}

/// <summary>
///
/// </summary>
public virtual string Sa2condp
{
get { return _sa2condp; }
set
{
if ( value != null)
if( value.Length > 50)
throw new ArgumentOutOfRangeException("Invalid value for Sa2condp", value, value.ToString());

_isChanged |= (_sa2condp != value); _sa2condp = value;
}
}

/// <summary>
///
/// </summary>
public virtual DateTime Sa2datpr
{
get { return _sa2datpr; }
set { _isChanged |= (_sa2datpr != value); _sa2datpr = value; }
}

/// <summary>
///
/// </summary>
public virtual DateTime Sa2datpf
{
get { return _sa2datpf; }
set { _isChanged |= (_sa2datpf != value); _sa2datpf = value; }
}

/// <summary>
///
/// </summary>
public virtual DateTime Sa2datdv
{
get { return _sa2datdv; }
set { _isChanged |= (_sa2datdv != value); _sa2datdv = value; }
}

/// <summary>
///
/// </summary>
public virtual string Sa2obspc
{
get { return _sa2obspc; }
set
{
if ( value != null)
if( value.Length > 150)
throw new ArgumentOutOfRangeException("Invalid value for Sa2obspc", value, value.ToString());

_isChanged |= (_sa2obspc != value); _sa2obspc = value;
}
}

/// <summary>
/// Returns whether or not the object has changed it's values.
/// </summary>
public virtual bool IsChanged
{
get { return _isChanged; }
}

/// <summary>
/// Returns whether or not the object has changed it's values.
/// </summary>
public virtual bool IsDeleted
{
get { return _isDeleted; }
}

#endregion


#region Public Functions

/// <summary>
/// mark the item as deleted
/// </summary>
public virtual void MarkAsDeleted()
{
_isDeleted = true;
_isChanged = true;
}


#endregion


#region Equals And HashCode Overrides
/// <summary>
/// local implementation of Equals based on unique value members
/// </summary>
public override bool Equals( object obj )
{
if( this == obj ) return true;
if( ( obj == null ) || ( obj.GetType() != this.GetType() ) ) return false;
Sa2pocot castObj = (Sa2pocot)obj;
return ( castObj != null ) &&
( this._sa2codpc == castObj.Sa2codpc );
}

/// <summary>
/// local implementation of GetHashCode based on unique value members
/// </summary>
public override int GetHashCode()
{

int hash = 57;
hash = 27 * hash * _sa2codpc.GetHashCode();
return hash;
}
#endregion

}
}

--------------------------------------------------------------------------------------
Salminat:

hbm.xml:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MapeamentoOR.Salminat,MapeamentoOR" table="SALMINAT">

<id name="Salidemn" column="SALIDEMN" type="Decimal" unsaved-value="0">
<generator class="assigned"/>
</id>
<property column="SALDSMIN" type="String" name="Saldsmin" length="40" />

</class>
</hibernate-mapping>



cs:

using System;

namespace MapeamentoOR
{
/// <summary>
/// Generated by MyGeneration using the NHibernate Object Mapping adapted by Gustavo
/// </summary>

[Serializable]
//public sealed class Salminat
public class Salminat
{
#region Private Members
private bool _isChanged;
private bool _isDeleted;
private decimal _salidemn;
private string _saldsmin;
#endregion

#region Default ( Empty ) Class Constuctor
/// <summary>
/// default constructor
/// </summary>
public Salminat()
{
_salidemn = 0;
_saldsmin = string.Empty;
}
#endregion // End of Default ( Empty ) Class Constuctor

#region Public Properties

/// <summary>
///
/// </summary>
public virtual decimal Salidemn
{
get { return _salidemn; }
set { _isChanged |= (_salidemn != value); _salidemn = value; }
}

/// <summary>
///
/// </summary>
public virtual string Saldsmin
{
get { return _saldsmin; }
set
{
if ( value != null)
if( value.Length > 40)
throw new ArgumentOutOfRangeException("Invalid value for Saldsmin", value, value.ToString());

_isChanged |= (_saldsmin != value); _saldsmin = value;
}
}

/// <summary>
/// Returns whether or not the object has changed it's values.
/// </summary>
public virtual bool IsChanged
{
get { return _isChanged; }
}

/// <summary>
/// Returns whether or not the object has changed it's values.
/// </summary>
public virtual bool IsDeleted
{
get { return _isDeleted; }
}

#endregion


#region Public Functions

/// <summary>
/// mark the item as deleted
/// </summary>
public virtual void MarkAsDeleted()
{
_isDeleted = true;
_isChanged = true;
}


#endregion


#region Equals And HashCode Overrides
/// <summary>
/// local implementation of Equals based on unique value members
/// </summary>
public override bool Equals( object obj )
{
if( this == obj ) return true;
if( ( obj == null ) || ( obj.GetType() != this.GetType() ) ) return false;
Salminat castObj = (Salminat)obj;
return ( castObj != null ) &&
( this._salidemn == castObj.Salidemn );
}

/// <summary>
/// local implementation of GetHashCode based on unique value members
/// </summary>
public override int GetHashCode()
{

int hash = 57;
hash = 27 * hash * _salidemn.GetHashCode();
return hash;
}
#endregion

}
}


--------------------------------------------------------------------------------------

Sagcdstt:

hbm.xml:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="MapeamentoOR.Sagcdstt,MapeamentoOR" table="SAGCDSTT">

<id name="Sagidest" column="SAGIDEST" type="Decimal" unsaved-value="0">
<generator class="assigned"/>
</id>
<property column="SAGTPSTS" type="String" name="Sagtpsts" length="40" />

</class>
</hibernate-mapping>


CS:

using System;

namespace MapeamentoOR
{
/// <summary>
/// Generated by MyGeneration using the NHibernate Object Mapping adapted by Gustavo
/// </summary>
[Serializable]
public class Sagcdstt
{
#region Private Members
private bool _isChanged;
private bool _isDeleted;
private decimal _sagidest;
private string _sagtpsts;
#endregion

#region Default ( Empty ) Class Constuctor
/// <summary>
/// default constructor
/// </summary>
public Sagcdstt()
{
_sagidest = 0;
_sagtpsts = null;
}
#endregion // End of Default ( Empty ) Class Constuctor

#region Public Properties

/// <summary>
///
/// </summary>
public virtual decimal Sagidest
{
get { return _sagidest; }
set { _isChanged |= (_sagidest != value); _sagidest = value; }
}

/// <summary>
///
/// </summary>
public virtual string Sagtpsts
{
get { return _sagtpsts; }
set
{
if ( value != null)
if( value.Length > 40)
throw new ArgumentOutOfRangeException("Invalid value for Sagtpsts", value, value.ToString());

_isChanged |= (_sagtpsts != value); _sagtpsts = value;
}
}

/// <summary>
/// Returns whether or not the object has changed it's values.
/// </summary>
public virtual bool IsChanged
{
get { return _isChanged; }
}

/// <summary>
/// Returns whether or not the object has changed it's values.
/// </summary>
public virtual bool IsDeleted
{
get { return _isDeleted; }
}

#endregion


#region Public Functions

/// <summary>
/// mark the item as deleted
/// </summary>
public virtual void MarkAsDeleted()
{
_isDeleted = true;
_isChanged = true;
}


#endregion


#region Equals And HashCode Overrides
/// <summary>
/// local implementation of Equals based on unique value members
/// </summary>
public override bool Equals( object obj )
{
if( this == obj ) return true;
if( ( obj == null ) || ( obj.GetType() != this.GetType() ) ) return false;
Sagcdstt castObj = (Sagcdstt)obj;
return ( castObj != null ) &&
( this._sagidest == castObj.Sagidest );
}

/// <summary>
/// local implementation of GetHashCode based on unique value members
/// </summary>
public override int GetHashCode()
{

int hash = 57;
hash = 27 * hash * _sagidest.GetHashCode();
return hash;
}
#endregion

}
}


--------------------------------------------------------------------------------------

In the default.aspx i have a gridView.

I´ve set it columns using the method RetornaPoco:

public static IList RetornaPoco(string column, int maximumRows, int startRowIndex)
{

try
{

ISession session = NHibernateHelper.GetCurrentSession();

//string hql = "from Sa2pocot as Poco";

string hql = "SELECT Poco.Sa2codpc, Poco.Salminat.Saldsmin, Poco.Salminat.Sagcdstt. FROM Sa2pocot.Sagtpsts AS Poco";

//sorting
if (!String.IsNullOrEmpty(column))
{
hql += " ORDER BY Poco." + column;
}

IQuery query = session.CreateQuery(hql);

int conta = 0;
conta= query.List().Count;
//paging
query.SetFirstResult(startRowIndex);
query.SetMaxResults(maximumRows);

return query.List();
}
catch (Exception e)
{
throw e;
}


}


I want to display int to the gridView the fields:

Sa2codpc, Saldsmin and Sagtpsts.


But now the error message above is displayed:

undefined alias or unknown mapping: Sa2pocot [SELECT Poco.Sa2codpc, Poco.Salminat.Saldsmin, Poco.Salminat.Sagcdstt. FROM Sa2pocot.Sagtpsts AS Poco]
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: NHibernate.QueryException: undefined alias or unknown mapping: Sa2pocot [SELECT Poco.Sa2codpc, Poco.Salminat.Saldsmin, Poco.Salminat.Sagcdstt. FROM Sa2pocot.Sagtpsts AS Poco]


I think that I have explained the problem in a better way.

If you don't understand please tell me.


Thanks in advance!


Regards,

You can't put a property into the from clause, I don't think. Sa2pocot.Sagtpsts isn't even a legitimate property, is it? What are you trying to do?

Also, please PLEASE try to formulate readable examples because the naming schema in your project is frankly unreadable.

Hi welennAraujo!

select Poco.Sa2codpc, Poco.Salidemn.Saldsmin, Poco.Sagidest.Sagtpsts from Sa2pocot AS Poco

In select clause you have to refer alias as first part of name (Poco in example above), and properties as all the rest parts of the name - NHibernate can't and won't lookup all the properties to find where is some class mentioned (moreover you may have several properties that refer to the same class). So you have to put Salidemn and Sagidest as the second part of the names (not class names!) the rightmost part of the names in this case is simple - it is property name - BUT in some cases, you may have "empty" last part - in example below you may have "select Poco, ..." - so first element in result will be corresponding object itself. The same rule apply to from clause - so when you write "from Sa2pocot.Sagtpsts" NHibernate will try to lookup in collection Sagtpsts of class Sa2pocot. But IMHO it won't work, as at least one element in from list must be "just class" - to serve as a root item, to which all the rest items will be "joined".

You definitely have to read and understand HQL part of documentation! There are some good examples there.

[quote="WelennAraujo"] Hi IgorK!

Thanks once again for the reply.

I tried it !

The error message "A field or property with the name 'Sa2codpc' was not found on the selected data source" is displayed.

I have an IList method called RetornaPoco:

public static IList RetornaPoco(string column, int maximumRows, int startRowIndex)
{

try
{

ISession session = NHibernateHelper.GetCurrentSession();

string hql = "select Poco.Sa2codpc, Poco.Salidemn.Saldsmin, Poco.Sagidest.Sagtpsts from Sa2pocot AS Poco";

//sorting
if (!String.IsNullOrEmpty(column))
{
hql += " ORDER BY Poco." + column;
}

IQuery query = session.CreateQuery(hql);

int conta = 0;
conta= query.List().Count;
//paging
query.SetFirstResult(startRowIndex);
query.SetMaxResults(maximumRows);

return query.List();
}
catch (Exception e)
{
throw e;
}


}

This field belongs to my database, its the Sa2pocot PK.

This method belongs to the class PocoDAO where i set my Database access rules.

My dataSource uses this method to display the rows as a select method as well.

I 've already read the documentation and also print it. But the examples are confuse and it uses just one table, even about the use of join.

Maybe I´m wrong, but working about 3 years using ANSI SQL makes me think that I´m not the only one.

Are my mapping classes correct?

While I was searching in internet about it I saw in some examples the tag <bag>. Must I use it?

Thanks my friend!

That's an interesting situation. I understand that NH is very good at being RDBMS independent, but good luck making the transition to production. There isn't an Oracle DB you can use for dev?



God Bless the DBAs, but see below. Your explanation/translation helped greatly, thank you.



Igor's a little more patient than I :)

Everything looks okay in your mapping and the query. It looks like NH can't find the property, which makes me guess there is a configuration problem. Maybe it's using the wrong mapping file?



I would definitely use caution with code generation. I used MyGeneration once a while ago. It may have improved since, but I found that it was no substitute for actually knowing how to do everything I needed to myself. They also usually needed tweaking to get the fullest use out of NH.

Another reason you may want to go with authoring your own classes and mappings is to be able to use a sensible naming system. Whatever reasons your DBA has, there is no reason the system he outlined for the DB needs to extend to your object layer.