Using a custom loader works in one of my scenarios, where have a many-
to-one, but I'm stuck on the many-to-many.
Consider these tables:
User
------
UserID
FName
...
User_x_Role
-------------------
ID
UserID (FK)
RoleID (FK)
Role
------
RoleID
Name
Description
Mappings:
<class name="User" table="UserInfo">
<id name="EntityId" column="userID" type="Int32" unsaved-value="0">
<generator class="native" />
</id>
<property name="FName" column="FNAME" />
...
<bag
name="Roles"
table="User_x_Role"
access="nosetter.pascalcase-m-underscore"
inverse="false"
>
<key column="UserID" />
<many-to-many class="Role" column="RoleID" />
</bag>
<!-- Override the SQL for SELECT to use the view (vw_users) -->
<loader query-ref="qryUserLoader" />
</class>
<!-- Override the SQL for SELECT to use the view (vw_users) -->
<sql-query name="qryUserLoader">
<return class="User" />
SELECT UserID, FNAME, ...
FROM dbo.vw_users
WHERE UserID = ?
</sql-query>
<class name="Role" table="Role">
<id name="EntityId" column="RoleID" type="Int32" unsaved-value="0">
<generator class="native" />
</id>
<property name="Name" column="Name" />
<property name="Description" column="Description" />
<bag
name="Users"
table="User_x_Role"
access="nosetter.pascalcase-m-underscore"
inverse="false"
>
<key column="RoleID" />
<many-to-many class="User" column="UserID" />
<!-- this needs to be overridden as well, to use the view
(vw_users); see case 504 -->
<loader query-ref="loader_Role_Users" />
</bag>
</class>
<sql-query name="loader_Role_Users">
<load-collection alias="u" role="Role.Users" />
SELECT
{u.*}
FROM
dbo.vw_users u
INNER JOIN dbo.User_x_Role uxr
ON u.UserID = uxr.UserID
WHERE
uxr.RoleID = :roleID
</sql-query>
The <loader> for the <bag> works fine in my other situation (many-to-
one), and I am able to correctly get results that come from
dbo.vw_users instead of dbo.UserInfo when I use Office.Users, but in
this case, Role.Users, I have a many-to-many with dbo.User,
dbo.User_x_Role, and dbo.Role, and the custom loader does not work.
I found these:
http://forum.hibernate.org/viewtopic.php?t=971496
http://jira.nhibernate.org/browse/NH-925
It seems that many-to-many custom loading is not currently supported.
Arrrg!
On Oct 2, 12:39 pm, MAMMON <[EMAIL PROTECTED]> wrote:
> In my reproducing the bug, I had this code:
>
> Role someRole = CurrentSession.Get<Role>(1);
> foreach(User user in someRole.Users)
> Console.WriteLine(user.FName);
>
> followed by
>
> RelatedObj myObj = CurrentSession.Get<RelatedObj>(1);
> User user = myObj.User;
> Console.WriteLine(user.FName);
>
> Well the 2nd block didn't actually go to the database and get the
> wrong values, it just used the cached data that was fetched from the
> 1st block. If I comment out the first block, I get the correct data
> from the 2nd one (meaning the correct SQL is generated, and the
> <loader> is properly used).
>
> Looks like I might be able to get away with just specifying custom
> <loader>'s for the <bag>'s after all =)
>
> I'll post again when I know for sure.
>
> On Oct 2, 12:33 pm, MAMMON <[EMAIL PROTECTED]> wrote:
>
> > That is unfortunate. I just reproduced the bug again, but without a
> > <bag>. In ANY class that is related to User, and has a User field/
> > property, the wrong SQL is generated.
>
> > RelatedObj myObj = CurrentSession.Get<RelatedObj>(1);
> > User user = myObj.User;
> > Console.WriteLine(user.FName);
>
> > This generates the wrong SQL as well. This would have the following
> > in RelatedObj.hbm.xml, in the <class> element.
>
> > <many-to-one name="User" column="userID" unique="true" />
>
> > I can't specify a custom loader in this case, so there is no way for
> > me to force the usage of vw_users.
>
> > I don't see how this can be by design. I think this is a genuine
> > bug. I specify a custom loader for the user class, and when user
> > objects are loaded via related entities, the loader is not used.
>
> > When I was originally faced with this Tables vs Views problem, I had 2
> > choices:
>
> > 1) Map to the view, and override INSERT, UPDATE and DELETE behavior to
> > use the table instead
>
> > 2) Map to the table, and override SELECT to use the view.
>
> > I went with #2, because in the NHibernate documentation, I read that
> > NHibernate will ignore <sql-insert> for a <class> if the <id> is
> > identity. All of ours use identity, so #1 wasn't an option.
>
> > On Oct 2, 12:21 pm, "Ayende Rahien" <[EMAIL PROTECTED]> wrote:
>
> > > I _think_ you need to specify a loader for the collection as well
>
> > > On Thu, Oct 2, 2008 at 10:17 PM, MAMMON <[EMAIL PROTECTED]> wrote:
>
> > > > In my NHibernate-based ordering system project, I have a User table
> > > > with common fields such as FName, LName, etc. We also have a table
> > > > named _tbl_AllUsers that is an import from our company's HR database
> > > > that has official data on our employees. Our ordering system has to
> > > > accommodate users not in the HR db, but it's nice when we can keep the
> > > > data sync'd. To reconcile the 2 sources of user data, we use a view,
> > > > vw_users, which does a COALESCE on overlapping fields.
>
> > > > SELECT
> > > > COALESCE(userInfo.FName, hr.FName) AS FName
> > > > ...
>
> > > > This way, we prefer data from our ordering system, but if it's not
> > > > their, we'll fall back on the HR database's data.
>
> > > > The problem with views like this is that they are read only. If my
> > > > User class (User.cs and User.hbm.xml) mapped to vw_users, then I'd
> > > > have read only functionality, and wouldn't be able to update user
> > > > records. So in User.nbm.xml, I map to the table, UserInfo, but then
> > > > use this in the <class> element:
>
> > > > <loader query-ref="qrySelectUser" />
>
> > > > which uses this query:
>
> > > > <!-- Override the SQL for SELECT to use the view (vw_users) -->
> > > > <sql-query name="qrySelectUser">
> > > > <return class="User" />
> > > > SELECT UserID, INTERNALID, FNAME, LNAME, MI, EMAIL,
> > > > PHONENUMBER,
> > > > TITLE, EMPLOYEEID, LoginID, LoginDomain, UserSourceType, IsVerified,
> > > > DateCreated, OfficeID
> > > > FROM dbo.vw_users
> > > > WHERE UserID = ?
> > > > </sql-query>
>
> > > > This works like a charm. When I load up users, I get results from the
> > > > view. When I create, update, or delete users, since the User class is
> > > > mapped to dbo.UserInfo, that's the table that gets updated.
>
> > > > The problem comes in when User objects get populated some OTHER way.
> > > > Consider this bag in the User class:
>
> > > > <bag
> > > > name="Roles"
> > > > table="User_x_Role"
> > > > access="nosetter.pascalcase-m-underscore"
> > > > inverse="false"
>
> > > > <key column="UserID" />
> > > > <many-to-many class="Role" column="RoleID" />
> > > > </bag>
>
> > > > And then consider loading up a User object like this:
>
> > > > Role someRole = CurrentSession.Get<Role>(1);
> > > > foreach(User user in someRole.Users)
> > > > Console.WriteLine(user.FName);
>
> > > > In this case, the User objects aren't using the <loader> in the
> > > > User.hbm.xml. They get loaded from the association depicted in the
> > > > <bag> above, and the User objects aren't being populated from the
> > > > view. The SQL generated by NHibernate (I did a trace) uses the
> > > > UserInfo table. This seems inconsistent (load a user from vw_users in
> > > > one case, but from dbo.UserInfo in another), but perhaps it is by
> > > > design for some necessary reason. Here's a cleaned up snippet of the
> > > > SQL from the trace:
>
> > > > SELECT
> > > > uxr.RoleID,
> > > > uxr.UserID,
> > > > ui.userID,
> > > > ui.FNAME,
> > > > ...
> > > > FROM
> > > > dbo.User_x_Role uxr
> > > > left outer join dbo.UserInfo ui
> > > > on uxr.UserID = ui.userID
> > > > WHERE
> > > > uxr.RoleID = 4
>
> > > > So how do I reconcile this? I would have thought that the <loader>
> > > > that causes SELECTs to come from the view would still override the SQL
> > > > generated in this case, but it doesn't. Do I have to create a custom
> > > > <loader> for the <bag> that forces the use of vw_users over
> > > > dbo.UserInfo?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
