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.

In my opinion this is something you could probably best do using a view in the database, that should be easy to create and then you can use the view in llblgen and page using that typed view.