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