Hello, Ayende!
Using <sql-query>, I came up with this:
<sql-query cacheable="true" name="Hierarchy">
<return class="L" alias="l"/>
<![CDATA[
;WITH Hierarchy(ID, Name, Parent)
AS
(
SELECT e.ID, e.NAME, e.PARENT
FROM Person e
WHERE e.ID = :id
UNION ALL
SELECT e.ID, e.NAME, e.PARENT
FROM Person e
INNER JOIN Hierarchy eh ON e.PARENT =
eh.ID
)
SELECT {l.*}
FROM Hierarchy {l}
]]>
</sql-query>
My POCO is like this:
[Serializable]
public class Person
{
public virtual Int32 Id { get; private set; }
public virtual Person Parent { get; set; }
public virtual String Name { get; set; }
public virtual ISet<Person> Children { get; private set; }
}
And this mapping:
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="MyNamespace" assembly="MyAssembly" auto-import="true"
default-access="property">
<class name="Person" table="`PERSON`">
<id name="Id" column="`ID`">
<generator class="identity"/>
</id>
<natural-id mutable="false">
<property name="Name" column="`NAME`" unique="true"/>
</natural-id>
<many-to-one name="Parent" column="`PARENT`" outer-join="true"
fetch="join"/>
<set name="Children" inverse="true" fetch="join" cascade="all-
delete-orphan" outer-join="true">
<key column="`PARENT`" foreign-key="`ID`"/>
<one-to-many class="Person"/>
</set>
</class>
</hibernate-mapping>
Now, using this command:
var personHierarchy = session.GetNamedQuery("Hierarchy").SetParameter
("id", 1).SetResultTransformer(Transformers.AliasToBean<Person>
()).List<Person>();
I get this exception:
PropertyAccessException: Exception occurred getter of
NHibernateTest.L.Id
TargetException (inner exception): Object does not match target type
Perhaps NHibernate is trying to set property Parent, of type Person,
from an Int32 (column PARENT)?
Is there any way I can accomplish this?
Thanks,
RP
On Sep 8, 10:23 pm, Ayende Rahien <[email protected]> wrote:
> Yes, that is how you usually would handle such things, because it is
> specific for each dialect.
> Another option is to change the schema so you can perform hierarchical
> queries using standard relations, in effect, de-normalizing the
> association.
>
> On Sep 7, 9:36 pm, Ricardo Peres <[email protected]> wrote:
>
>
>
> > Hi,
>
> > I have asked this before, but got no answer, so here I go again: what
> > is the best way to perform hierarchical queries, such as Oracle's
> > CONNECT BY PRIOR and SQL Server's WITH?
>
> > I thought about having SQL queries (<sql-query> elements) and then
> > using a AliasToBean transformer, the problem is that I would need
> > different queries for each DB engine.
>
> > Thanks,
>
> > RP
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
