Thanks a lot! Worked like a charm. I'll be sure to check out those links. On Saturday, January 26, 2013 9:21:25 PM UTC-6, William Green wrote: > > Let's break down what your query is doing: > I want UserEntities, > with their Building, > and all their Departments, > as a list. > > You're asking NHibernate to, in a single database query, fetch the a > parent and all of it's children. You're asking for a Cartesian Product; > that is, each department record would also have all the data for the > user_entity record. That means that UserEntities that have more than 1 > Department would have its data returned multiple times. > > This article explains the details much better than I can: > http://puredotnetcoder.blogspot.co.uk/2011/07/using-eager-loading-can-produce.html > (Also be sure to check out the linked article on efficient eager loading > of many child collections: > http://ayende.com/blog/4367/eagerly-loading-entity-associations-efficiently-with-nhibernate > ) > > The short of it is, you must use a ResultTransformer. Specifically, a > DistinctRootEntityResultTransformer, like so: > > var query = QueryOver.Of<UserEntity>() > .Fetch(x => x.Building).Eager > .Fetch(x => x.Departments).Eager > *.TransformUsing(Transformers.DistinctRootEntity);* > var users = query.GetExecutableQueryOver(session) > .Future<UserEntity>() > .OrderBy(x => x.FirstName) > .ThenBy(x => x.LastName); > > > Also notice that I moved the sorting from the database to the client. > Unless you need to sort the results in the db for a summary function, your > DBA will be much happier if you did the sort on the client side. (See > http://datachomp.com/archives/hey-app-quit-wasting-my-time-sorting-your-data/ > ) > > -- > Will Green > > On Friday, January 25, 2013 7:01:30 PM UTC-5, Tristin Forbus wrote: >> >> I'm new to Fluent NHibernate and working with databases in general, so >> I'm not sure what I should Google for. >> I have a many-to-many relationship (User HasManyToMany Departments). When >> I QueryOver User, I would like for the Departments to be loaded into >> User.Departments. What happens now if a user has multiple departments is I >> get two users back with the same IDs, but each has one department. >> >> I would like my result to look like this: >> UserA.Departments = ["Department1", "Department2"] // actual departments >> of the user. >> >> But after my query, I get something like this: >> [... {UserA.Departments=["Department1"]}, >> {UserA.Departments=["Department2"]} ... ] >> >> I've attached my mappings and the query. If anyone can tell the name of >> what I'm trying to do, that would be awesome. >> >> >> public class UserEntityOverride : IAutoMappingOverride<UserEntity> >> { >> public void Override(AutoMapping<UserEntity> mapping) >> { >> mapping.HasManyToMany(x => x.Departments) >> .Table("UserDepartment") >> .ParentKeyColumn("UserId") >> .ChildKeyColumn("DepartmentId") >> .Cascade.AllDeleteOrphan(); >> >> mapping.References(x => x.Supervisor); >> mapping.References(x => x.Building); >> } >> } >> >> var query = QueryOver.Of<UserEntity>() >> .Fetch(x => x.Building).Eager >> .Fetch(x => x.Departments).Eager >> .OrderBy(x => x.FirstName).Asc >> .ThenBy(x => x.LastName).Asc; >> var users = >> query.GetExecutableQueryOver(session).Future<UserEntity>().ToList(); >> >
-- You received this message because you are subscribed to the Google Groups "Fluent NHibernate" group. To post to this group, send email to fluent-nhibernate@googlegroups.com. To unsubscribe from this group, send email to fluent-nhibernate+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/fluent-nhibernate?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
