NHibernate CreateAlias ​​- joins arbitrary columns

Question

This question seems to be raised a bit, and I have not yet found a good answer. I have two classes without a foreign key and no real relationship, except for a common field, in this case "Title".

This is freely based on an example that I recently pulled from an outdated application, I am not allowed to change the schema, so just adding a foreign key is not an option. All I'm looking for is a query that will provide all the prerequisites for a course with a given name:

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

I am not looking for a comparison, for example Join (), HasMany (), etc., since all of them obviously require a certain relationship. I want to join two tables based on arbitrary columns without matching.

A similar question asked here , it seems to indicate that this is possible with CreateAlias ​​(), but I did not find 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 came up with, but it does not 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 pretty easy to do with LinqToSql, is it possible to do this with the Linq provider for NHibernate? The examples that I saw seem to indicate that the vendor basically throws a request made regarding the use of ICriteria / ICriterion NH magic - this is not possible, but please correct me if I am wrong.

Answer 1

One way is to create a separate criterion and execute it through a sub-request.

 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 create the following sql where clause: -

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