I have two tables related many to many: Members and Tasks. Each member can have multiple tasks, and each task can be assigned to multiple members. There is a TaskMember relation table that contains both PKs.
If my tables look like this:
Member
1, Bob
2, Joe
Task
1, Clean room
2, Do homework
3, Do dishes
MemberTask
1, 1
1, 2
2, 2
2, 3
What I want is a resulting PAGED list of unique tasks that looks like this:
Task....................... Assigned To
Clean room............ Bob
Do homework........ Bob, Joe
Do dishes.............. Joe
According to the documentation, typed lists (my preferred approach) do not support m:n relations, so this does not seem to be possible. Even if it was supported, in order to get back all the data I need it would give me back four rows, one for each relation, which is not what I want (I should also mention that I'm trying to accomplish this by binding the list to a data grid, and I really don't want to have additional logic just to parse the collection and combine duplicates).
The entity collection approach seems like it should be able to handle the relation aspect OK, but then I realized that you cannot use prefetch paths with paging So I can get my list of task entities, but the members collection of each task is empty since I could not specify to fill that prefetch path.
What is the recommended approach in this case? You could obviously make an additional call per row to retrieve extra data via whatever relations you want, but that doesn't really make sense performance-wise. In this case, the "Assigned To" column is really for display purposes only, so I was considering adding a text column that would contain a string like "Bob, Joe" and just update it from the relations each time the task gets updated, but I wanted to know if I was missing some way to accomplish this using the existing entities. Adding an extra text column is not exactly an elegant solution.