Ignore that last post!!
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
-~----------~----~----~----~------~----~------~--~---
