I have the following tables
TUser (user_id,firstname,lastname) PK user_id
TGroup (group_id,name) PK group_id
TUsersGroups (user_id,group_id) PK (user_id,group_id) FK (user_id) FK (group_id)
I need to bind to a DataGrid the TUsers, showing the following columns firstname,lastname and groupname (the NAME taken from the TGroup.
I know I can accomplish this via the Subsonic Query, but is there a way to get the list of type TUserCollection showing the Group Name?
Not like you want. However, if your list is small, you can just access the associated TGroup object from each of the TUser objects. But of course that causes another query each time you access one of them.
What I have done in the past is either add a column to the result set of a Subsonic Query (before the query is executed), but that means you don't have a strongly typed collection of TUser objects.
Also, you could create a view with all the columns you want and regen Subsonic.
Finally, you could pull back all users into a collection (that match your criteria) and all groups that match your criteria, into their own collections, then "find" the group you want out of the new Group collection whenever you need it for an associated TUser.
