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