Mapping trees is complicated in NHibernate...
The value in order-by must be the "physical" name of a column (or a SQL
expression). Have you tried using something that returns the order, such as
ROW_NUMBER() OVER(ORDER BY column) as a formula column? This is
DB-dependent, of course.
RP
On Friday, April 12, 2013 4:44:50 PM UTC+1, Andre wrote:
>
> 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.
