What is the preferred way of using unsupported SQL functions or clauses with Castle/NHibernate?

views:

answers:

+1 Q:

What is the preferred way of using unsupported SQL functions or clauses with Castle/NHibernate?

I modeled all of my tables and already ported most of my queries using detached criteria's. All is working very well however I'm at a point where I don't know how to advance. I have a sub-query that uses Oracle's "connect by" clause. How do developers typically overcome that limitation of Castle/NHibernate?

I am using the latest version of NHibernate.

+1 A:

If you really need to use connect by, you can always use a SQL Query (and there's a whole chapter about it).

However, given NHibernate's usage of the Identity Map pattern, is usually more productive to just load the objects and let the relationships be established in memory.

Diego Mijelshon 2010-07-23 01:36:51

Do you have an example of loading parent/child relationships with the usage of the identity map pattern? I'm not sure if you meant looping through each row finding the children.

Mike 2010-07-23 02:50:58

Just load the whole set, and the parents will be linked (assuming you've defined many-to-one relationships)

Diego Mijelshon 2010-07-23 02:58:41

Alternatively, you can load the elements *and* the child collection, which is less efficient on the DB but you get a ready-to-use tree.

Diego Mijelshon 2010-07-23 03:03:34

Sorry, but I still don't follow how I can load every child given a parent in one swoop without doing some sort of loop.

Mike 2010-07-23 15:35:11

Well, no. You can't load all the children *given a parent*, but how big is your table? Because it probably makes sense to load/cache all of its contents (the usual example for hierarchical structures is a navigation structure, and those are usually manageable)

Diego Mijelshon 2010-07-23 15:52:38

My table has 553,000~ rows. The table structure is not something I can change.

Mike 2010-07-23 16:42:20

How deep is your average subtree? If it's just a few levels, you should use batch-size in the children collection set to something like avg(childcount)+stddev(childcount), and you'll have +/- one query per level (you'd just get a parent and navigate down). If your graph is usually deep, you'll have to go with connect by and some additional plumbing (maybe NH is not the best tool in that case)

Diego Mijelshon 2010-07-23 17:30:49

That's hard to answer because a parent can have as little as 1 children and as many as several thousands. And the tree is growing every week. Think what I'll end up doing is simply modelling the table in question with no self-referencing relationship. I'll simply map every column in my model and if I need to fetch a tree (not always the case because something I need something else) I'll simply use a native SQL query. Then I could set the result transformer to that model and have a generic list of all children from that parent.Thank you very much. Much appreciated. :)

Mike 2010-07-23 17:48:13

ansaurus

tags:

views:

answers:

What is the preferred way of using unsupported SQL functions or clauses with Castle/NHibernate?

related questions