NHibernate CreateAlias - Joins on arbitrary columns

Question

This question seems to come up a bit and I've yet to see a good answer. I have two classes with no foreign key and no real relationship other than a common field, in this case "Title".

This is loosely based on an example I'd recently pulled from a legacy application, I am not allowed to modify the schema so simply adding a foreign key isn't an option. All I'm looking for is a query that would provide all the prerequisites for a course with a given title:

 select p.* from course c join prereq p on c.title = p.title

I'm not looking for a mapping like Join(), HasMany(), etc, since they all apparently require a defined relationship. I'm looking to join two tables based upon arbitrary columns with no mapping.

A similar question asked here a while back seems to indicate that it's possible using CreateAlias() but I haven't found any good examples.

   <class name="Course" table="course">
      <id name="id" column="id" type="long">
        <generator class="identity" />
      </id>
      <property name="Title" column="Title" type="String" />
    </class>

    <class name="Prereq" table="prereq">
      <id name="id" column="id" type="long">
        <generator class="identity" />
      </id>
      <property name="Title" column="Title" type="String" />
      <property name="PrereqTitle" column="PrereqTitle" type="String" />
    </class>

This is what I've come up with, but it doesn't seem to work. Any suggestions?

        var results = session.CreateCriteria(typeof(Prereq)) 
            .CreateAlias("Course", "C")
            .CreateAlias("Prereq", "P")
            .Add( Expression.EqProperty("C.Title", "P.Title")) 
            .Add( Expression.Eq("C.Title", "Course With Prereq"))
            .List();

This is easy enough to do with LinqToSql, can it possibly be done with the Linq provider for NHibernate? The examples I've seen seem to indicate that the provider basically boils a query done to whatever ICriteria/ICriterion magic is used by NH - it doesn't appear to be possible but please correct me if I'm mistaken.

Answer 1

One way would be to create a detached criteria and perform a exists via a sub query.

var dc = DetachedCriteria.For<Course>("c")
    .SetProjection(Projections.Property("c.Title"))
    .Add(Restrictions.EqProperty("c.Title", "p.Title"));

return Session.CreateCriteria<Prereq>("p")
    .Add(Subqueries.Exists(dc)).List<Prereq>();

This would generate the following sql where clause:-

WHERE exists (SELECT title as y0_
                   FROM   Course this_0_
                   WHERE  this_0_.Title = this_.Title)