I think I found the problem. 'Position' is a reserved keyword in ODBC. When NHibernate's hbm2ddl.keyword property is set to anything other than none, this will cause the issue I described.
I've posted a more detailed answer with a couple of workarounds here: http://stackoverflow.com/a/15986280/1231072 The short answer: either quote the order-by columns explicitly (`Position`), use a non-reserved word (e.g. PositionInParent) or set hbm2ddl.keyword to none (and use SchemaMetadataUpdater.QuoteTableAndColumns(configuration)) Am Freitag, 12. April 2013 20:18:28 UTC+2 schrieb Ricardo Peres: > > 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.
