Let's start with a sample database that has three tables:

 

 

The idea is that a task may have many resources and a resource can be assigned many tasks. This is a classic many-to-many relationship.

To begin, we need to create some stored procedures that will work with each side of the relationship. Like this:

 

CREATE PROCEDURE [dbo].[sp_GetResourcesForTask]
@TaskID int
AS
SELECT Resources.ResourceID, Resources.FirstName, Resources.LastName
FROM Resources
INNER JOIN ResourceTasks
ON Resources.ResourceID = ResourceTasks.ResourceID
INNER JOIN Tasks
ON ResourceTasks.TaskID = Tasks.TaskID
WHERE Tasks.TaskID = @TaskID

 

CREATE PROCEDURE [dbo].[sp_GetTasksForResource]
@ResourceID int
AS
SELECT Tasks.TaskId,Tasks.Title,Tasks.Description,Tasks.DueDate, Tasks.Status
FROM Resources
INNER JOIN ResourceTasks
ON Resources.ResourceID = ResourceTasks.ResourceID
INNER JOIN Tasks
ON ResourceTasks.TaskID = Tasks.TaskID
WHERE Resources.ResourceID = @ResourceID

 

Once the stored procedures are created, we can go to SPD and build the ECTs. We will build an ECT for "Task" and "Resource". To keep it simple, we'll start by building a set of operations for each ECT directly against the the table. This will give us a Finder, SpecificFinder, Creator, Updater, and Deleter method for each ECT.

 

After creating the base operations, we will create a new Finder method based on the stored procedures. We will use the GetResourcesForTask to return a view of all resources assigned to a task, and the GetTasksForResource to return all tasks assigned to a resource. The key is to create a Comparison filter for the input parameter in each stored procedure. When you create an External List, you can set the TaskId or ResourceID in the view to show just the tasks for a particular resource or the resources for a particular task. The following image shows the results in the External Data Web Parts.

 

In order to improve the user experience, you are likely going to have to move to custom development. In particular, you are going to have to create a .NET Assembly Connector and a set of custom web parts for display.

 

While SPD is a great tool for no-code BCS solutions, it only supports Finder, SpecificFinder, Creator, Deleter, Updater, and AssociationNavigator method stereotypes. There are many others available that you can only use in custom code. Creating a .NET Assembly Connector allows you to make use of other stereotypes. In particular, you can create "Foreign key-less" associations - which is the basic support for many-to-many associations. The following image shows the tooling in Visual Studio 2010 for this.

 

 

 

Along with creating associations, you can also create Associator and Disassociator method which essentially add and remove entries to the ResourceTasks table that is representing the relationships between the entities. Here is the implementation code for the Associator and Disassociator in my .NET Assembly Connector solution. Note that the inputs are just Task and Resource IDs.

 

        public static void ResourceTaskAssociator(int resourceID, int taskID)
        {
            ActionItemsDatabase ctx = new ActionItemsDatabase(GetConnectionString());

            var r1 = (from r in ctx.Resources
                      where r.ResourceID == resourceID
                      select r).FirstOrDefault();
            var t1 = (from t in ctx.Tasks
                      where t.TaskID == taskID
                      select t).FirstOrDefault();
            ResourceTask rt = new ResourceTask() { Task = t1, Resource = r1 };
            ctx.AddToResourceTasks(rt);
            ctx.SaveChanges();
        }

        public static void ResourceTaskDisassociator(int resourceID, int taskID)
        {
            ActionItemsDatabase ctx = new ActionItemsDatabase(GetConnectionString());
            var r1 = (from r in ctx.Resources
                      where r.ResourceID == resourceID
                      select r).FirstOrDefault();
            var t1 = (from t in ctx.Tasks
                      where t.TaskID == taskID
                      select t).FirstOrDefault();
            ResourceTask rt = new ResourceTask() { Task = t1, Resource = r1 };
            ctx.DeleteObject(rt);
            ctx.SaveChanges();
        }