This question is locked. New answers and comments are not allowed.
Hi there,
I got stuck in working with many-to-many relations in combination with OA/LinQ and need some advice on it. I've read the docs and forums but can't find (or see) a solution, although I guess this is a daily business scenario:
I have two persistent classes named Users and Groups, each containing a IList<> of the other type. A user belongs to 0-n groups and a group has 0-n members. Nothing special so far. Using forward mapping I setup a managed collection and my DB contains a join table Groups_Users containing both table's PKs, exactly the way I would have created my tables manually. Nice!
Initially I want my UI to show a Gridview (Winforms) listing all my groups with their members in Child-Templates. My approach: I need to brake down my many-to-many relation into a 1:n relation from the groups point of view to feed the gridview's child template, So I thought a BindingList of anonymous types containing User+Group ID would fit as a datasource for the child template.
In SQL I would have queried my list this way (stripped down to IDs):
SELECT grp.groups_id, usr.users_id FROM groups grp
LEFT OUTER JOIN Groups_Users rel ON (rel.groups_id=grp.groups_id)
LEFT OUTER JOIN Users usr ON (usr.users_id=rel.users_id)
With this as Child-Data I could relate to the master template using groups_id, so the gridview would work like intended (well, I guess so).
So, a couple of questions arise here:
-How to do this in LINQ? I can't do scope.Extent<Groups_Users>() as there isn't a Groups_Users class (or am I supposed to write one?), or is there another way to define the joins?
- Am I heading in the right direction or is there a total different approach I should try? I tried to set all Users as Child datasource and create a relation using the Group.Members collection, but no luck so far.
Thanks in advance for any help
Stefan
I got stuck in working with many-to-many relations in combination with OA/LinQ and need some advice on it. I've read the docs and forums but can't find (or see) a solution, although I guess this is a daily business scenario:
I have two persistent classes named Users and Groups, each containing a IList<> of the other type. A user belongs to 0-n groups and a group has 0-n members. Nothing special so far. Using forward mapping I setup a managed collection and my DB contains a join table Groups_Users containing both table's PKs, exactly the way I would have created my tables manually. Nice!
Initially I want my UI to show a Gridview (Winforms) listing all my groups with their members in Child-Templates. My approach: I need to brake down my many-to-many relation into a 1:n relation from the groups point of view to feed the gridview's child template, So I thought a BindingList of anonymous types containing User+Group ID would fit as a datasource for the child template.
In SQL I would have queried my list this way (stripped down to IDs):
SELECT grp.groups_id, usr.users_id FROM groups grp
LEFT OUTER JOIN Groups_Users rel ON (rel.groups_id=grp.groups_id)
LEFT OUTER JOIN Users usr ON (usr.users_id=rel.users_id)
With this as Child-Data I could relate to the master template using groups_id, so the gridview would work like intended (well, I guess so).
So, a couple of questions arise here:
-How to do this in LINQ? I can't do scope.Extent<Groups_Users>() as there isn't a Groups_Users class (or am I supposed to write one?), or is there another way to define the joins?
- Am I heading in the right direction or is there a total different approach I should try? I tried to set all Users as Child datasource and create a relation using the Group.Members collection, but no luck so far.
Thanks in advance for any help
Stefan