NHibernate QueryOver: How to join unrelated entities?

Question

I have the following query working which gets the results I want:

int associatedId = 123;

MyObject alias = null;

var subQuery = QueryOver.Of<DatabaseView>()
    .Where(view => view.AssociatedId == associatedId)
    .And(view => view.ObjectId == alias.ObjectId)
    .Select(view => view.ObjectId);

var results = session.QueryOver<MyObject>(() => alias)
    .WithSubquery.WhereExists(subQuery)
    .List();

The DatabaseView has been mapped as an actual NHibernate entity (so I can use it with QueryOver), but it is not associated to MyObject in the HBM mappings.

This query returns an IList<MyObject> using a SELECT ... FROM MyObject WHERE EXISTS (subquery for DatabaseView here). How can I re-write this to return the same data but using a JOIN instead of sub query?

Answer 1

In NHibernate 5.1+ it's possible for QueryOver/Criteria via Entity Join:

int associatedId = 123;

MyObject alias = null;
DatabaseView viewAlias = null;

var results = session.QueryOver<MyObject>(() => alias)
    .JoinEntityAlias(() => viewAlias, () => viewAlias.ObjectId == alias.ObjectId && viewAlias.AssociatedId == associatedId)
    .List();

Criteria example:

int associatedId = 123;
var results = session.CreateCriteria<MyObject>("alias")
    .CreateEntityAlias(
            "viewAlias",
            Restrictions.EqProperty("viewAlias.ObjectId", "alias.ObjectId")
            && Restrictions.Eq("viewAlias.AssociationId", associatedId),
            JoinType.InnerJoin,
            typeof(DatabaseView).FullName)
    .List();

Answer 2

You can join onto unrelated entities with Linq in NHibernate 3+

Funnily enough you use the join query expression element:

from type1 in Repository.Query<MyType1>() 
join type2 in Repository.Query<MyType2>() 
on type1.Id equals type2.Id

Note: Repository.Query is just returning an IQueryable Query from the session

I'm hoping there is a solution for QueryOver as I don't always want to model two-way relationships in my domain but they are still useful for querying.

Also, you can map a Access="noop" 2 way relationship using Criteria API without putting into your POCO classes:

http://ayende.com/blog/4054/nhibernate-query-only-properties