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?
Best Answer
In NHibernate 5.1+ it's possible for QueryOver/Criteria via Entity Join:
Criteria example: