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?

Clamorous

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();

Garrick

I like this one because I don't need to add Remotion.Linq. Thanks – Westfalen

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

Osteitis

This actually works when joining with two IQueryable instances. I am amazed that this works with the LINQ (Query<>) implementation, but not with QueryOver<> – Wiedmann

Answer 3

I realize this question is 5 years old, and the "correct" answer is definitely that you can't do this with QueryOver, as the other answers indicate. However, if you really need this functionality (as I did), there is a decent workaround that I found.

The solution is to use a "loader query" with native SQL in your mapping XML to produce a related collection (see http://nhibernate.info/doc/nhibernate-reference/querysql.html#querysql-load). In the OP's specific example, you would go ahead and map your DatabaseView as an entity as suggested, and then write the following in your mapping:

<class name="MyObject"...>
    ...
    <set name="MyViews" inverse="true">
        <key column="ObjectId" foreign-key="none"/>
        <one-to-many class="MyObject"/>
        <loader query-ref="myObjectViewsLoadQuery"/>
    </set>
</class>

Then we just need to define our named myObjectViewsLoadQuery in raw SQL to explain to NH how to join the two:

<sql-query name="myObjectViewsLoadQuery">
    <load-collection alias="view" role="MyObject.MyViews"/>
    SELECT view.*
    FROM DatabaseView view
    WHERE view.ObjectId = :id
</sql-query>

We can now pretend like there is a "real" collection named MyViews relating MyObject to DatabaseView in our query:

MyObject alias = null;
DatabaseView view = null;
var results = session.QueryOver<MyObject>(() => alias)
     .JoinAlias( () => alias.MyViews, () => view )
     //.Where( () => view.Property == "myValue" ) // optionally, restrict the view etc.
     .List();

Certainly, this is a lot of trouble to go through if you only care about an "elegant" query. However, if the reason you are using QueryOver is that you want to be able to accept arbitrary input Expressions to filter your DatabaseView by, or various similar activities, this works very nicely.

Hay