ASP.NET Weblogs

Welcome to ASP.NET Weblogs Sign in | Join | Help
in Search

Josh Schwartzberg high-fives the CLR

and plays with his shiny golden hammer....

NHibernate querying without mapping inverse relationships

In my neverending quest to keep my domain minimal, I ran into a query that I wanted to perform with NHibernate that seems to be impossible without adding an additional property and hbm mapping definition.

Note: I still consider myself rather new to NHibernate, so this might have an obvious answer.

Let's say I have following two classes:
public class School : EntityBase
{
     public string Name { get; set; }
     public IList<Student> Students { get; set; }
}

public
class Student : EntityBase
{

    
public string Name { get; set; }
}

The School.hbm.xml file contains this in the body:
<bag name="Students">
    <
key column="SchoolId"/>
    <
one-to-many class="Student"/>
</
bag>

The database representation of the above looks something like this:
Db representation

Without making any changes to my classes/mappings, I can perform this SQL query to retrieve all Students who's name contains 'Powers' that belong to a school who's name contains 'Middle':
SELECT * FROM Students
WHERE
     Students
.Name LIKE '%Powers%'
    
AND
    
Students.SchoolId IN (SELECT School.Id FROM Schools WHERE Schools.Name LIKE '%Middle%')

So - Here is the question, how can I do this in an HQL query without making any changes to my domain (which would be specifically adding a School property to the Student class and defining the relationship in the Student.hbm.xml file)?

Published Feb 28 2008, 11:00 AM by dotjosh
Filed under: ,

Comments

 

Dan Goldstein said:

The best solution to this problem really is to create the inverse mapping. It probably would have taken less time than it did to post and you'll probably need it anyway. Keeping your domain minimal is a good plan but you're just inflating it and making it more difficult to maintain by having a wacky HQL query in there. That doesn't answer your question, but it solves your problem.

February 28, 2008 2:08 PM
 

Ayende Rahien said:

You probably want to use the inverse anyway. Right now inserting a new student is two operations, instead of one, because NH has to update two reference.

And it doesn't really make sense from a domain model point of view.

That said, this HQL would solve the issue:

select s from Student s

where s.Name like '%Powers%'

and s in (select i from School sc join sc.Students i)

February 28, 2008 4:18 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit