Possible to create an arbitrary association in criteria?

MikeB · **Joined:** Mon Feb 04, 2008 7:36 pm **Posts:** 31

Hibernate version:
2.1.0 Alpha 1

Is there a way to do arbitrary joins in NHibernate?

For example

db tables
[A].ID
[A].BID <- id of b class

[B].ID

POCO:
A.BID
B.ID

Mapping:

Code:

  <class name="A" table="A">
    <id name="ID" type="int" unsaved-value="0">
      <generator class="native" />
    </id>
   <property name="BID" type="int"/>
  </class>

  <class name="B" table="B">
    <id name="ID" type="int" unsaved-value="0">
      <generator class="native" />
    </id>
  </class>

Now, I want to get a list of all [a] types that match my b.ID in a critiera query.

crit = session.CreateCriteria(typeof(A))
.createCritieria("B", "B")
.Add(Expression.IdEq(4));

The problem is, NHIbernate does not know how to get from object a to object b (there is no mapping).

How can I say something like:
crit = session.CreateCriteria(typeof(A))
.createCritieria("B", "B").On(Expression.Eq(A.ID, B.ID)
.Add(Expression.IdEq(4));

Obviously my domain is more complex than this, the question really is how to join two classes that do not have a mapped association using a critieria query.

Thanks

nissim_levy · **Joined:** Wed Aug 27, 2008 9:08 pm **Posts:** 4

You can't do it. NHibernate is an ORM not a relational database. You are stuck with the object relations you defined.

Perhaps you can write a Stored Procedure and then call that Stored Procedure from NHibernate. I think you can do that with NHibernate 1.2 and up - although I've never done it myself.

wolli · **Posted:** Fri Aug 29, 2008 2:51 am

Basically I agree with nissim_levy, but in some cases you can do that using a correlated subquery. You need a detached criteria for that:

Code:

DetachedCriteria dc = DetachedCriteria.For<B>("b")
 .Add(Expression.PropertyEq("a.Id", "b.Id"))
 .Add(Expression.Eq("b.Id", 4))
 .SetProjection(Projections.Id());

ICriteria crit = session.CreateCriteria(typeof(A),"a")
   .Add(Subqueries.Exists(dc));

MikeB · **Joined:** Mon Feb 04, 2008 7:36 pm **Posts:** 31

That is sort of the conclusion I came to. There are two motivations for this functionality,

one: people can create plugins to my system so I cant actually link the one to many side of the relationship, there is no collection property on my class for the plugin class.

two: I have a table with a million rows in it, this has a many-to-one relationship to a parent table. I don't want to create a lazy loaded one-to-many property on the parent (totally avoiding the loading of one million child rows which is not a valid use case for our system).

I arrived at the conclusion that wolli stated, using a sub-query. The problem is, if you combine both motivations from above, you end up having to nest subqueries, not ideal from a readability standpoint by still functional.

Lastly I am going to be writing a dynamic query writer for my NHibenate implementation and the limitation of no dynamic joins complicates the issue.

Thanks for the help