Re: [fluent-nhib] Fluent mapping with "StartsWith"

[Geoff Bennett]

I might be not fulling understanding what you're asking here, but if what you want to do is just retrieve all descendants based on some kind of separated path, can't you just do that with an NHibernate criteria query? Something like:

var descendants = Session.CreateCriteria<OrgUnit>()

.Add(Restrictions.InsensitiveLike("HierarchicalPath", pathRootSegment, MatchMode.BeginsWith)**

.List<OrgUnit>();

Assuming the path is something like: Parent/Child/Child/Child/Child etc, then for the pathRootSegment = "Parent/Child" this would match:

Parent/Child/

Parent/Child/Child/

Parent/Child/Child/Child/

Parent/Child/Child/Child/Child

Then you could just use a HasMany mapping on each OrgUnit if you wanted to interrogate the direct children of any given unit.

Again, sorry if I'm misunderstood the question.

** I'm not sure if that's the correct match mode, but there is a "begins with" equivalent, and if your case is constant, then you can just use Like instead of InsensitiveLike.

Thanks,

Geoff.

On Friday, 10 August 2012 at 5:46 AM, Michael Vogt wrote:

Hello,

I use fluent and nHibernate for about 2 months now but can not find the right documentation for my problem:

I habe a table "OrgUnits" wich represents a tree that can easy be queried. I have the following columns:

Id (Uniqueidentifier)

Name (string)

ParentOrgUnitId (uniqueidentifier)

HierarchicalPath (string - the complete path is resolved using a configured seperator)

I want to avoid any recursion and have a query, based on the HierarchicalPath column that can select all descendant org units (not only the children). I need this recursion-less query to resolve some connected ACL's (Access Control Lists).

I think there is some solution to query for all descendants in linq, but: How can I MAP this descendant org units. There is no Primary-Foreign Key relationship to all descendants. It is easy to create an SQL to query for all descendents (the select is cheap if you have an index in "HierarchicalPath":

I do not know if I can tell the fluent "HasMany" statement not to use any primary-foreign-key relationship but instead give some "StartsWith" on the HierarchicalPath column.

Here is the SQL that collects all org units I want to be returned by the "Descendants" mapping. This selects all descendant org units including "MyRoot".

Is it possible to do a mapping (self-mapping) that serves a IEnumerable<OrgUnit> property with all org units defined in the SQL below using Fluent?

select d.* from OrgUnits o
left join OrgUnits d on CharIndex(o.HierarchicalPath,d.HierarchicalPath)=1
where o.Name='MyRoot'

--
You received this message because you are subscribed to the Google Groups "Fluent NHibernate" group.
To view this discussion on the web visit https://groups.google.com/d/msg/fluent-nhibernate/-/I5w4iWDx9sQJ.
To post to this group, send email to fluent-n...@googlegroups.com.
To unsubscribe from this group, send email to fluent-nhibern...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en.

[Michael Vogt]

Thank you a lot, Geoff!!

OK, my missing link is:

How is the criteria query applied to the HasMany call, There is no method on HasMany where I can apply such a (criteria) query. Every usage of HasMany I tried creates or uses a foreign-key-relationship in the underlying tables and I do not need key-relationships in my case. I did not find a goot documentation for HasMany until now.

Meanwhile I have a solution that is fast enough implemented in my repository (not in the map). The trick was not to use linq "join" but use 

                var linq = from ou in CurrentSession.Query<OrgUnit>()

                           from ouDesc in CurrentSession.Query<OrgUnit>()

                           where

                           ouDesc.Id==ou.Id ||ouDesc.HierarchicalPath.StartsWith(ou.HierarchicalPath)

                           select ouDesc;

               

[Geoff Bennett]

No problems. The query I included in my original reply should do exactly what you asked. You don't need to traverse the parent/child relationships if you have the hierarchy path in a property of the object.

I'm assuming, based on your model, that all the OrgUnits are stored in one table with self-referential links. By issuing the criteria query in my email, it will return all of the OrgUnits in the hierarchy based on the path, not the relationship, although this will mimic what you would get if you wrote a recursive routine to walk the tree.

As for the foreign key being added when you specify a has many, that shouldn't matter. It's a natural way to ensure RI and that the NH relationship will work. If you don't use it, it's cognitive cost to you is zero. I'm not aware of a way to have a HasMany without one.

My reasoning for mentioning the HasMany was just incase you wanted to interrogate the children of any given OrgUnit directly. If you simply want to build a visual tree structure, then you can just order by the path hierarchy property as part of the criteria query, then loop over the result set adding the respective children to their parent.

Thanks,

Geoff.

To view this discussion on the web visit https://groups.google.com/d/msg/fluent-nhibernate/-/Nup2qPb2I-UJ.

[Michael Vogt]

Thank's again!

I ended in using the query in my repository and quit searching for a better solution in my mapping - not only because I didn't found a solution but: Returning every descendant (not only the children) from a tree in a map can result in loading too much data into the applications memory. I think it's better to use a repository method for the developers to enforce some boundary.

Regards and an effective and efficient week to every reader and specially to Geoff!

Michael