Hi! Seems it not possible to use the with keyword on fetched associations, obvisouly :).
See http://nhforge.org/doc/nh/en/index.html#manipulatingdata-filtering for using with lazy: ICollection userrecords = session.CreateFilter( result.userreocrds, "where this.userid = :userid").setInt ("userid", userId) ); See http://nhforge.org/doc/nh/en/index.html#filters for filter when join fetching. On Nov 28, 11:53 pm, Schotime <[email protected]> wrote: > hey, > > i have two tables. one called records and one called userrecords. > records has the relationship that a record contains multiple > userrecords. > > table records: > recordid int identity, > details varchar > > table userrecords > userrecordid int identity, > recordid int, > userid int, > extradetails varchar > > Will explain with an example. > if there are 10 records and 10 users then you can expect to see 100 > user records. One for each combination. A unique index exists on the > userrecords table constraining this. > > So if you were to right the query in sql it would be..assuming you are > after the records and user records for user 2. > > select * from records r > left outer join userrecords ur on r.recordid = ur.recordid and > ur.userid = 2 > > this will join the tables correctly so that it will still give you one > user record for each record. If you included the userid as a filter > and there was only 5 user records for user 2 then you would only get 5 > rows returned and this would not be correct. > > Therefore i tried to right this in hql. > > from records r > left join fetch r.userrecords with r.userid = :userid > > However I was given the error. > > Semantic exception in sqlhqlwalker.cs "with-clause not allowed on > fetched associations; use filters" > > If i remove the fetch the query works but then the lazy queries don't > include the userid so the data returned is incorrect. > > I'm only new to nhibernate but consider myself fairly competent with > sql. So if there is a better way to structure my model etc, or I > should be doing it another way then I'm open to suggestions too. If > you need more info let me know. > > Many thanks, > Adam -- 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.
