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
-~----------~----~----~----~------~----~------~--~---
