Another NHibernate JOIN problem.
I'm trying to join two different properties from one table by different two keys. But I can't get the second JOIN property.
Simplified example -
My class -
namespace Domain
{
public class Message
{
#region private Members
private string _id;
private string _senderID;
private string _recipientID;
private string _recipientName;
private string _senderName;
#endregion
#region Public Properties
public virtual string ID
{
get { return _id; }
set { _id = value; }
}
public virtual string ID
{
get { return _id; }
set { _id = value; }
}
public virtual string SenderID
{
get { return _senderID; }
set { _senderID= value; }
}
public virtual string RecipientID
{
get { return _recipientID; }
set { _recipientID= value; }
}
public virtual string SenderName
{
get { return _senderName; }
set { _senderName= value; }
}
public virtual string RecipientName
{
get { return _recipientName; }
set { _recipientName= value; }
}
#endregion
#region Constructors
public Message()
{
_id = Guid.NewGuid().ToString();
}
#endregion
}
}
Mapping -
<class name="Domain.Message" table="Messages" >
<id name="ID">
<column name="OID"/>
<generator class="assigned"/>
</id>
<property name="SenderID" unique="true">
<column name="SenderID" unique="true"/>
</property>
<property name="RecipientID" unique="true">
<column name="RecipientID" unique="true"/>
</property>
<join table="CompanyData" optional="true" >
<key column="CompanyID" property-ref="SenderID" />
<property name="SenderName" column="CompanyName" unique="true" lazy="false"/>
</join>
<join table="CompanyData" optional="true" >
<key column="CompanyID" property-ref="RecipientID" />
<property name="RecipientName" column="CompanyName" unique="true" lazy="false"/>
</join>
</class>
but I get the following SQL -
SELECT this_.OID as OID30_0_, this_.SenderID as Sender30_0_,
this_.RecipientID as Recipient30_0_, this_1_.CompanyName as SiteID9_0_
FROM Messages this_
left outer join CompanyData this_1_ on
this_.SenderID=this_1_.CompanyID
left outer join CompanyData this_2_ on
this_.RecipientID=this_2_.CompanyID
And I want -
SELECT this_.OID as OID30_0_, this_.SenderID as Sender30_0_,
this_.RecipientID as Recipient30_0_, this_1_.CompenyName as
SiteID9_0_ , this_2_.CompanyName as SiteID10_0_
FROM Messages this_
left outer join CompanyData this_1_ on
this_.SenderID=this_1_.CompanyID
left outer join CompanyData this_2_ on
this_.RecipientID=this_2_.CompanyID
I'm using NHibernate 3.2
Thanks
I was lurking thru the internet all day long to find solution to same problem. What I found is thread on hibernate board. I took solution from Ashkan Aryan . So if anyone else gets headaches about solution and don't want to use views I will post my code which I am using now.
I have to use from 1 to 12 joins on same table so creating views is to much confusing.
private void addParagraphsQuery(DetachedCriteria sourceQuery, List<ParagraphContentArgument> paragraphsArguments)
{
DetachedCriteria dc;
Conjunction conjunction = Restrictions.Conjunction();
string alias = string.Empty;
if (paragraphsArguments != null && paragraphsArguments.Count > 0)
{
for (int i = 0; i < paragraphsArguments.Count; i++)
{
alias = "p" + i.ToString();
dc = DetachedCriteria.For<Document>().SetProjection(Projections.Id());
dc.CreateAlias("paragraphList", alias);
dc.Add(Restrictions.Eq(alias + ".paragraphSectionTemplate", paragraphsArguments[i].ParagraphTemplate));
dc.Add(Restrictions.Like(alias + ".content", paragraphsArguments[i].Argument, MatchMode.Anywhere));
conjunction.Add(Property.ForName("id").In(dc));
}
}
sourceQuery.Add(conjunction);
}
Regards,
Mariusz
Apparently, this cannot be done at the moment with NHibenate mappings. The closest solution, suggested by Spencer Ruport, is to create a view and map the object to it.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With