NHibernate QueryOver for combining unrelated objects

Question

I have the following working query that 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 was displayed as the actual NHibernate object (so I can use it with QueryOver), but it is not related to MyObject in HBM mappings.

This query returns an IList using SELECT ... FROM MyObject WHERE EXISTS (subquery for DatabaseView here). How can I rewrite this to return the same information, but use JOIN instead?

Answer 1

You can join unrelated objects with Linq in NHibernate 3+

Oddly enough, you are using 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 just returns an IQueryable Query from a session

I hope there is a solution for QueryOver, as I do not always want to model two-way relationships in my domain, but they are still useful for queries.

In addition, you can map the 2-way Access = "noop" relationships using the criteria API without entering the POCO classes:

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

Answer 2

I understand that this question is 5 years old, and the “correct” answer is definitely that you cannot do this with QueryOver, as the other answers indicate. However, if you really need this feature (like me), there is a suitable workaround that I found.

The solution is to use a “loader query” with native SQL in your XML mapping to create the appropriate collection (see http://nhibernate.info/doc/nhibernate-reference/querysql.html#querysql-load ). In a specific OP example you should go and display your DatabaseView as an object, as suggested, and then write the following in your display:

 <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 called myObjectViewsLoadQuery in raw SQL to explain 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> 

Now we can pretend that in our request there is a “real” collection called MyViews , relating MyObject to DatabaseView :

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

Of course, this is a lot of trouble if you only care about the "elegant" request. However, if the reason you use QueryOver is because you want to be able to accept arbitrary input expressions to filter your DatabaseView or various similar actions, this works very well.

Answer 3

In NHibernate 5.1, this is possible for QueryOver 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(); 

Answer 4

You cannot do this with QueryOver, Criteria or Linq. The only way to do this is through HQL.

http://www.codewrecks.com/blog/index.php/2009/09/04/theta-join-in-hql-join-with-unrelated-entities/