Modeling Associations in External Data

A Business Data Connectivity (BDC) model can include associations between external content types (ECTs). An association is a relationship that allows the BDC runtime to navigate between external content types. You can create two different types of association between external content types:

• Foreign key association. This type of association maps a field in one external content type ECT (the foreign key) to an identifier in another ECT.
• Foreign keyless association. This type of association uses custom logic to relate one ECT to another ECT.

For example, suppose you create an ECT named Customers, with an identifier of CustomerID. You also create an ECT named Orders, which includes a CustomerID field. You create a foreign key association from the Orders ECT to the Customers ECT, as shown in the following diagram. Note that the field types must be the same on both sides of the association.

Foreign key association between external content types

This association allows you to create rich user interfaces using built-in BCS components. For example, you can create a profile page for a customer that automatically shows all of their orders in a Business Data Related List. The association would also allow you to use external item picker controls to select a customer when you create an order.

Just like the stereotyped operations that define an ECT, associations are created as methods in the BDC model. Each association includes an input parameter, which must be an identifier on the destination ECT, and a set of return parameters. In the previous example, CustomerID is the input parameter and OrderID and Amount are likely choices for return parameters. The association method could be expressed as "Get me all the orders where the CustomerID is equal to the specified value."

SharePoint Designer enables you to interactively create the following types of associations:

• One-to-one foreign key associations. One item in the destination table relates to one item in the source table. For example, you could use a one-to-one association to model the relationship between an employee and a department.
• One-to-many foreign key associations. One item in the destination table relates to many items in the source table. For example, you could use a one-to-many association to model the relationship between a customer and their orders.
• Self-referential foreign key associations. One item in the source table relates to other items in the source table. For example, you could use a self-referential association to model relationships between people.

All of these associations are expressed declaratively as non-static methods in the BDC model. However, in the following cases, you will need to edit the BDC model XML directly:

• One-to-one, one-to-many, or many-to-many foreign keyless associations. This refers to any association that cannot be modeled directly by a foreign key relationship.
• Multiple ECT associations. This refers to any association that returns fields from more than one ECT.
• Non-integer primary keys. This refers to any entity that has a primary key that is not an integer.

In each of these cases, it's worth using SharePoint Designer to take the model as far as you can, before you export the .bdcm file and manually edit the XML. Foreign keyless associations always require custom logic to define the relationship between the ECTs. This custom logic could be a stored procedure in a database, a Web service, or a method in a .NET connectivity assembly. Typical scenarios in which you might require a foreign keyless association include when you need to navigate between entities that are related by an intermediate table. The following are some examples:

• You use an intermediate table to model a many-to-many relationship. For example, you might have a many-to-many relationship between parts and machines—a machine contains many parts, and a part is found in many machines.
• A customer is related to an order, and an order is related to a line item. You want to show the customer in a line item view.

For example, the following diagram illustrates a foreign keyless association between OrderLines and Customers. The foreign keyless association would use a stored procedure to navigate back to Customers through the Orders table.

Foreign keyless association between external content types

You can only create associations between entities in the same BDC model. For more information about creating associations in Visual Studio 2010, see Creating an Association Between Entities, Authoring BDC Models, and Creating External Content Types and Associations on MSDN. For more general information about creating associations, see Association Element in MethodInstances, The Notion of Associations and the External Item Picker, and Tooling Associations in SharePoint Designer 2010.

How Are Associations Defined in the BDC Model?

It can be instructive to take a look at how associations are represented in the underlying BDC model. For example, SharePoint Designer created the following association method, which represents a foreign key association between an InventoryLocations entity and a Parts entity. Notice the automatically generated Transact-SQL in the RdbCommandText property. Defined on the InventoryLocations entity, the association method allows you to navigate from a specific Parts instance to the related InventoryLocations instance by providing a PartSKU parameter.

<Method IsStatic="false" Name="InventoryLocationsNavigate Association">
  <Properties>
  <Property Name="BackEndObject" Type="System.String">InventoryLocations
    </Property>
<Property Name="BackEndObjectType" Type="System.String">SqlServerTable
    </Property>
    <Property Name="RdbCommandText" Type="System.String">
      SELECT [ID] , [PartSKU] , [BinNumber] , [Quantity] 
      FROM [dbo].[InventoryLocations] 
      WHERE [PartSKU] = @PartSKU
    </Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data,    
      Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text
    </Property>
    ...

The method definition also defines every parameter, but these have been omitted for brevity. Within the method definition, the method instance defines the specific logic that is actually invoked to traverse the association. Note that the method instance has a Type attribute value of AssociationNavigator, which is common to all methods that navigate an association. Also note the SourceEntity and DestinationEntity elements that actually define the association. A single method definition could contain multiple method instances, each defining an association between InventoryLocations and another entity (providing that all the associations are based on the PartSKU field).

  ...  
  <MethodInstances>
    <Association Name="InventoryLocationsNavigate Association" 
                 Type="AssociationNavigator" 
                 ReturnParameterName="InventoryLocationsNavigate Association" 
                 DefaultDisplayName="InventoryLocations Navigate Association">
      <Properties>
    <Property Name="ForeignFieldMappings" 
                  Type="System.String">
&lt;?xml version="1.0" encoding="utf-16"?&gt;
&lt;ForeignFieldMappings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
&lt;ForeignFieldMappingsList&gt;
&lt;ForeignFieldMapping ForeignIdentifierName="SKU" ForeignIdentifierEntityName="Parts" ForeignIdentifierEntityNamespace="DataModels.ExternalData.PartsManagement" FieldName="PartSKU" /&gt;
&lt;/ForeignFieldMappingsList&gt;
&lt;/ForeignFieldMappings&gt;
        </Property>
      </Properties>
      <SourceEntity Namespace="DataModels.ExternalData.PartsManagement" 
                    Name="Parts" />
      <DestinationEntity Namespace="DataModels.ExternalData.PartsManagement" 
                         Name="InventoryLocations" />
    </Association>
  </MethodInstances>
</Method>

The encoded XML within the ForeignFieldMappings element can be hard to read. Essentially, this identifies the foreign key—in other words, the field in the Parts entity that provides the PartSKU parameter. Decoded and simplified, the field value resembles the following.

<ForeignFieldMappings>
  <ForeignFieldMappingsList>
    <ForeignFieldMapping ForeignIdentifierName="SKU" 
                         ForeignIdentifierEntityName="Parts" 
                         ForeignIdentifierEntityNamespace=
                           "DataModels.ExternalData.PartsManagement" 
                         FieldName="PartSKU" />
  </ForeignFieldMappingsList>
</ForeignFieldMappings>

Compare this association to a manually-defined foreign keyless association. The following association method defines an association between a Machines entity and a Parts entity. Because this is a many-to-many relationship, you need to specify a stored procedure to navigate the association. In this case, the RdbCommandText property identifies the stored procedure to call.

<Method IsStatic="false" Name="GetPartsByMachineID">
  <Properties>
<Property Name="BackEndObject" Type="System.String">GetPartsByMachineID
    </Property>
<Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine
    </Property>
<Property Name="RdbCommandText" Type="System.String">
      [dbo].[GetPartsByMachineID]
    </Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data,  
      Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
      StoredProcedure
    </Property>
    ...

Again, the method definition also defines each parameter, but these have been omitted for brevity. Because the relationship is not based on a foreign key, the method instance definition does not need to identify foreign key mappings. Instead, it simply specifies the SourceEntity and the DestinationEntity.

  ...
  <MethodInstances>
    <Association Name="GetPartsByMachineID" 
                 Type="AssociationNavigator" 
                 ReturnParameterName="GetPartsByMachineID" 
  DefaultDisplayName="Parts Read With Sproc">
      <SourceEntity Namespace="DataModels.ExternalData.PartsManagement" 
                    Name="Machines" />
      <DestinationEntity Namespace="DataModels.ExternalData.PartsManagement" 
                         Name="Parts" />
    </Association>
  </MethodInstances>
</Method>

To explore these entities and associations in more detail, see the External Data Models reference implementation.