NHibernate CreateSqlQuery and object graph
- by magellings
Hello I'm a newbie to NHibernate. I'd like to make one sql query to the database using joins to my three tables.
I have an Application with many Roles with many Users. I'm trying to get NHibernate to properly form the object graph starting with the Application object. For example, if I have 10 application records, I want 10 application objects and then those objects have their roles which have their users. What I'm getting however resembles a Cartesian product in which I have as many Application objects as total User records.
I've looked into this quite a bit and am not sure if it is possible to form the application hierarchy correctly. I can only get the flattened objects to come back. It seems "maybe" possible as in my research I've read about "grouped joins" and "hierarchical output" with an upcoming LINQ to NHibernate release. Again though I'm a newbie.
[Update Based on Frans comment in Ayende's post here I'm guessing what I want to do is not possible http://ayende.com/Blog/archive/2008/12/01/solving-the-select-n1-problem.aspx ]
Thanks for you time in advance.
Session.CreateSQLQuery(@"SELECT a.ID,
a.InternalName,
r.ID,
r.ApplicationID,
r.Name,
u.UserID,
u.RoleID
FROM dbo.[Application] a JOIN dbo.[Roles] r ON a.ID = r.ApplicationID
JOIN dbo.[UserRoleXRef] u ON u.RoleID = r.ID")
.AddEntity("app", typeof(RightsBasedSecurityApplication))
.AddJoin("role", "app.Roles")
.AddJoin("user", "role.RightsUsers")
.List<RightsBasedSecurityApplication>().AsQueryable();
