Creating an Association Between Entities
You can define relationships between entities in your Business Data Connectivity (BDC) model by creating associations. Visual Studio generates methods that provide consumers of the model with information about each association. These methods can be consumed by SharePoint Web Parts, lists, or custom applications to display data relationships in a user interface (UI).
Creating an Association
Create an association by selecting an Association in the Visual Studio Toolbox, clicking the first entity (called the source entity) and then clicking the second entity (called the destination entity). You can define the details of the association in the Association Editor. For more information, see How to: Create an Association between Entities.
Association Methods
Applications such as SharePoint Business Data Web Parts consume associations by calling methods in the service class of an entity. You can add methods to the service class of an entity by selecting them in the Association Editor.
By default, the Association Editor adds an Association Navigation method to the source and destination entities. An Association Navigation method in the source entity enables consumers to retrieve a list of destination entities. An Association Navigation method in the destination entity enables consumers to retrieve the source entity that relates to a destination entity.
You must add the code to each of these methods to return the appropriate information. You can also add other types of methods to support more advanced scenarios. For more information about each of these methods, see Supported Operations.
Types of Associations
You can create two types of associations in the BDC designer: foreign key-based associations and foreign keyless associations.
Foreign Key-Based Association
You can create a foreign key-based association by relating an identifier in the source entity to type descriptors defined in the destination entity. This relationship enables consumers of the model to provide an enhanced UI for their users. For example, a form in Outlook that enables a user to create a sales order that can display customers in a drop-down list; or a list of sales orders in SharePoint that enables users to open a profile page for a customer.
To create a foreign key-based association, relate identifiers and type descriptors that share the same name and type. For example, you might create a foreign key-based association between a Contact entity and a SalesOrder entity. The SalesOrder entity returns a ContactID type descriptor as part of the return parameter of Finder or Specific Finder methods. Both type descriptors appear in the Association Editor. To create a foreign key-based relationship between the Contact entity and SalesOrder entity, select the ContactID identifier next to each of these field.
Add code to the Association Navigator method of the source entity that returns a collection of destination entities. The following example returns the sales orders for a contact.
Public Shared Function ContactToSalesOrder(ByVal contactID As Integer) As IEnumerable(Of SalesOrderHeader)
Const ServerName As String = "MySQLServerName"
Dim dataContext As AdventureWorksDataContext = _
New AdventureWorksDataContext("Data Source=" & ServerName & _
";Initial Catalog=AdventureWorks;Integrated Security=True")
Dim orderList As IEnumerable(Of SalesOrderHeader) = _
From orders In dataContext.SalesOrderHeaders _
Where orders.ContactID = contactID _
Select orders
Return orderList
End Function
public static IEnumerable<SalesOrderHeader> ContactToSalesOrder(int contactID)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
IEnumerable<SalesOrderHeader> orderList =
from orders in dataContext.SalesOrderHeaders
where orders.ContactID == contactID
select orders;
return orderList;
}
Add code to the Association Navigator method of the destination entity that returns a source entity. The following example returns the contact that is related to the sales order.
Public Shared Function SalesOrderToContact(ByVal salesOrderID As Integer) As IEnumerable(Of Contact)
Const ServerName As String = "MySQLServerName"
Dim dataContext As AdventureWorksDataContext = _
New AdventureWorksDataContext("Data Source=" & ServerName & _
";Initial Catalog=AdventureWorks;Integrated Security=True")
Dim TempContactID As Integer = _
(From orders In dataContext.SalesOrderHeaders _
Where orders.SalesOrderID = salesOrderID _
Select orders.ContactID).[Single]()
Dim contactList As IEnumerable(Of Contact) = _
From contacts In dataContext.Contacts _
Where contacts.ContactID = TempContactID _
Select contacts
Return contactList
End Function
public static IEnumerable<Contact> SalesOrderToContact(int salesOrderID)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
int TempContactID = (from orders in dataContext.SalesOrderHeaders
where orders.SalesOrderID == salesOrderID
select orders.ContactID).Single();
IEnumerable<Contact> contactList = from contacts in dataContext.Contacts
where contacts.ContactID == TempContactID
select contacts;
return contactList;
}
Foreign Keyless Association
You can create an association without mapping identifiers to field type descriptors. Create this kind of association when the source entity does not have a direct relationship with the destination entity. For example, a SalesOrderDetail table does not have a foreign key that maps to a primary key in a Contact table.
If you want to display information in the SalesOrderDetail table that relates to a Contact, you can create a foreign keyless association between the Contact entity and SalesOrderDetail entity.
In the Association Navigation method of the Contact entity, return the SalesOrderDetail entities by joining tables, or by calling a stored procedure.
The following example returns details of all sales orders by joining tables.
Public Shared Function ContactToSalesOrderDetail(ByVal contactID As Integer) As IEnumerable(Of SalesOrderDetail)
Const ServerName As String = "MySQLServerName"
Dim dataContext As AdventureWorksDataContext = _
New AdventureWorksDataContext("Data Source=" & ServerName & _
";Initial Catalog=AdventureWorks;Integrated Security=True")
Dim orderList As IEnumerable(Of SalesOrderDetail) = _
From orders In dataContext.SalesOrderHeaders _
Join orderDetails In dataContext.SalesOrderDetails On _
orders.SalesOrderID Equals orderDetails.SalesOrderID _
Where orders.ContactID = contactID _
Select orderDetails
Return orderList
End Function
public static IEnumerable<SalesOrderDetail> ContactToSalesOrderDetail(int contactID)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
IEnumerable<SalesOrderDetail> orderList =
from orders in dataContext.SalesOrderHeaders
join orderDetails in dataContext.SalesOrderDetails on
orders.SalesOrderID equals orderDetails.SalesOrderID
where orders.ContactID == contactID
select orderDetails;
return orderList;
}
In the Association Navigation method of the SalesOrderDetail entity, return the related Contact. The following example demonstrates this.
Public Shared Function SalesOrderDetailToContact(ByVal salesOrderID As Integer, ByVal salesOrderDetailID As Integer) As IEnumerable(Of Contact)
Const ServerName As String = "MySQLServerName"
Dim dataContext As AdventureWorksDataContext = _
New AdventureWorksDataContext("Data Source=" & ServerName & _
";Initial Catalog=AdventureWorks;Integrated Security=True")
Dim TempContactID As Integer = _
(From orders In dataContext.SalesOrderHeaders _
Where orders.SalesOrderID = salesOrderID _
Select orders.ContactID).[Single]()
Dim contactList As IEnumerable(Of Contact) = _
From contacts In dataContext.Contacts _
Where contacts.ContactID = TempContactID _
Select contacts
Return contactList
End Function
public static IEnumerable<Contact> SalesOrderDetailToContact(int salesOrderID, int salesOrderDetailID)
{
const string ServerName = "MySQLServerName";
AdventureWorksDataContext dataContext = new AdventureWorksDataContext
("Data Source=" + ServerName + ";" +
"Initial Catalog=AdventureWorks;Integrated Security=True");
int TempContactID = (from orders in dataContext.SalesOrderHeaders
where orders.SalesOrderID == salesOrderID
select orders.ContactID).Single();
IEnumerable<Contact> contactList = from contacts in dataContext.Contacts
where contacts.ContactID == TempContactID
select contacts;
return contactList;
}
See Also
Tasks
How to: Create an Association between Entities