Here’s the scenario: you’ve got two Windows Communication Foundation (WCF) services that return related data. For instance, let one of them be a service that returns People and let the other be a service that returns Documents. Every Document was authored by one or more Person. Since these services are separate Line of Business (LOB) systems, you must input them separately into Business Connectivity Services (BCS). You want to associate these two items together. If you have a one-to-many relationship between them and you return the foreign key of the “many” entity, you can do this in SharePoint Designer without a problem.
Where it becomes difficult is if there isn’t a one-to-many relationship between entities that span LOB systems. SharePoint Designer won’t provide this connection for you, but will instead complain that either the destination is a collection or the source isn’t one. To complete this association, you must use Visual Studio.
Suppose you type the connections up in Visual Studio, or use the BDC Modeling tools, or export your model from SharePoint after loading it without the associations in SharePoint Designer. Now you want to add your associations directly in Visual Studio. Unfortunately, no matter what you do you’ll always run into the following error: “Associations cannot be made across Line of Business Systems”. However, you might be thinking: “I can make associations across Line of Business Systems if they’re foreign key relationships.” This is true, as evidenced by SharePoint Designer’s ability to do it, but if you export the BDC models for the systems with the association, you won’t find the association defined there. Instead you’ll just find that SharePoint should “discover” the entities attached to the LOB System. It would appear something is going on behind the scenes in the backend database, but getting to it is beyond what I want to muck with.
Instead, the least painful solution is to define a custom .NET assembly in a SharePoint feature that connects the two LOB systems together and makes the service calls for BCS. From there, you can define multiple entities from the same LOB system in the BDC model and everything should work swimmingly. The fluff can be accomplished in the BDC Modeling tools built into Visual Studio 2010. The underlying code can simply attach to your web service or directly to the back-end systems. To SharePoint, this looks like one LOB system, which is the distinction you want to make for this to happen.