Hello,
I'm having a bit of trouble when querying an entity and an associated
collection when the collection is sorted.
Consider this class that represents a node in a hierarchical structure:
public class Node
{
public Node()
{
Children = new List<Node>();
}
public virtual int Id { get; set; }
public virtual IList<Node> Children { get; set; }
public virtual Node Parent { get; set; }
public virtual int Position
{
get { return Parent == null ? -1 : Parent.Children.IndexOf(this); }
set { }
}
}
The mapping looks like this (as NHibernate does not support lists in
bidirectional associations, I use a bag here and have the children
determine their position automatically):
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:xsd="http://www.w3.org/2001/XMLSchema"; namespace="AmbiguousColumn"
assembly="AmbiguousColumn" xmlns="urn:nhibernate-mapping-2.2">
<class name="Node">
<id name="Id" type="Int32">
<generator class="identity" />
</id>
<bag name="Children" inverse="true" cascade="all" order-by="Position">
<key column="Parent" />
<one-to-many class="Node" />
</bag>
<many-to-one name="Parent" />
<property name="Position" />
</class>
</hibernate-mapping>
To get all nodes with their children loaded I'd use a query like this:
var nodes = session.QueryOver<Node>()
.Fetch(x => x.Children).Eager
.List();
However, executing this results in an exception:
NHibernate.Exceptions.GenericADOException: could not execute query
[...(sql)...] ---> System.Data.SqlClient.SqlException: Ambiguous column
name 'Position'.
The SQL:
SELECT
this_.Id as Id0_1_,
this_.Parent as Parent0_1_,
this_.Position as Position0_1_,
children2_.Parent as Parent3_,
children2_.Id as Id3_,
children2_.Id as Id0_0_,
children2_.Parent as Parent0_0_,
children2_.Position as Position0_0_
FROM
Node this_
left outer join
Node children2_
on this_.Id=children2_.Parent
ORDER BY
Position
I understand why this happens: NH joins the same table twice, but uses the
order clause without qualifying the column name.
The question is: how can I make this scenario work? Resorting to <list>
instead of <bag> is probably difficult as I'd like to have a bidirectional
relation.
Thanks a lot for your answers,
Andre
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/nhusers?hl=en-US.
For more options, visit https://groups.google.com/groups/opt_out.
