Mapping Association Sets to Stored Procedures (Entity Framework)
The ModificationFunctionMapping element within an AssociationSetMapping specifies the stored procedures that insert and delete instances of an association between entities within the Entity Framework. The ModificationFunctionMapping within an AssociationSetMapping is useful when mapping many-to-many associations usually supported by a link table in the database.
When mapping a foreign key relationship, such as the relationship between SalesOrderDetail and SalesOrderHeader in the AdventureWorks sample database, use the AssociationEnd binding technique in a ModificationFunctionMapping EntityTypeMapping. For more information, see Stored Procedure Support (Entity Framework).
The top-level element in an AssociationSetMapping is the ModificationFunctionMapping element. The DeleteFunction and InsertFunction child-elements describe parameter bindings for the ends of the association.
The FunctionName attribute for DeleteFunction and InsertFunction is, in each case, the name of the stored procedure being referenced in the storage model.
Note
There is no UpdateFunction element for association sets because associations cannot be modified; associations can only be created or deleted.
The EndProperty bindings declared in an association set's stored procedure mapping resemble the AssociationEnd element declared in an EntitySetMapping, but they require less context because the parent element indicates the AssociationSet. ScalarProperty child-elements describe parameter bindings for the key values in the association ends and the corresponding parameters of stored procedures.
The following example maps insert and delete functions that create and delete entries in a many-to-many link table that supports the Contact_Address association.
<AssociationSetMapping Name="Contact_Address"
TypeName="ContactInformationModel.Contact_Address"
StoreEntitySet="Contact_Address">
<EndProperty Name="Address">
<ScalarProperty Name="AddressID" ColumnName="AddressID" />
</EndProperty>
<EndProperty Name="Contact">
<ScalarProperty Name="ContactID" ColumnName="ContactID" />
</EndProperty>
<ModificationFunctionMapping>
<DeleteFunction
FunctionName="ContactInformationModel.Store.DeleteAddress">
<EndProperty Name="Address">
<ScalarProperty Name="AddressID" ParameterName="AddressID"/>
</EndProperty>
<EndProperty Name="Contact">
<ScalarProperty Name="ContactID" ParameterName="ContactID"/>
</EndProperty>
</DeleteFunction>
<InsertFunction
FunctionName="ContactInformationModel.Store.SetAddress">
<EndProperty Name="Address">
<ScalarProperty Name="AddressID" ParameterName="AddressID"/>
</EndProperty>
<EndProperty Name="Contact">
<ScalarProperty Name="ContactID" ParameterName="ContactID"/>
</EndProperty>
</InsertFunction>
</ModificationFunctionMappi
To create the database used in this example, run the following script.
USE [master]
GO
CREATE DATABASE [ContactInformation]
GO
USE [ContactInformation]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Address](
[AddressID] [int] NOT NULL,
[StreetAddress] [nvarchar](50) NOT NULL,
[City] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Contact]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Contact](
[ContactID] [int] NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED
(
[ContactID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Contact_Address]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Contact_Address](
[ContactID] [int] NOT NULL,
[AddressID] [int] NOT NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Address]') AND parent_object_id = OBJECT_ID(N'[dbo].[Contact_Address]'))
ALTER TABLE [dbo].[Contact_Address] WITH CHECK ADD CONSTRAINT [FK_Address] FOREIGN KEY([AddressID])
REFERENCES [dbo].[Address] ([AddressID])
GO
ALTER TABLE [dbo].[Contact_Address] CHECK CONSTRAINT [FK_Address]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Contact]') AND parent_object_id = OBJECT_ID(N'[dbo].[Contact_Address]'))
ALTER TABLE [dbo].[Contact_Address] WITH CHECK ADD CONSTRAINT [FK_Contact] FOREIGN KEY([ContactID])
REFERENCES [dbo].[Contact] ([ContactID])
GO
ALTER TABLE [dbo].[Contact_Address] CHECK CONSTRAINT [FK_Contact]
To create the stored procedures used in this example run the following scripts.
Use the following script to create the function used to create an instance of the association:
USE [ContactInformation]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.SetAddress', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.SetAddress;
GO
CREATE PROCEDURE [dbo].[SetAddress]
@ContactID int,
@AddressID int
AS
INSERT Contact_Address(ContactID, AddressID)
VALUES(@ContactID, @AddressID)
Use the following script to create the function used to delete an instance of the association:
USE [ContactInformation]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID ( 'dbo.DeleteAddress', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.DeleteAddress;
GO
CREATE PROCEDURE [dbo].[DeleteAddress]
@ContactID int,
@AddressID int
AS
Delete Contact_Address
WHERE ContactID = @ContactID
AND AddressID = @AddressID
The complete .edmx schema, shown below, has been modified manually to implement the AssociationSetMapping and ModificationFunctionMapping. The Entity Framework designer tools do not support implementation of the AssociationSetMapping of ModificationFunctionMapping.
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0"
xmlns:edmx="https://schemas.microsoft.com/ado/2007/06/edmx">
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="ContactInformationModel.Store" Alias="Self"
Provider="System.Data.SqlClient" ProviderManifestToken="2005"
xmlns:store="https://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">
<EntityContainer Name="dbo">
<EntitySet Name="Address"
EntityType="ContactInformationModel.Store.Address"
store:Type="Tables" />
<EntitySet Name="Contact"
EntityType="ContactInformationModel.Store.Contact"
store:Type="Tables" />
<EntitySet Name="Contact_Address"
EntityType="ContactInformationModel.Store.Contact_Address"
store:Type="Tables" store:Schema="dbo"
store:Name="Contact_Address">
<DefiningQuery>
SELECT
[Contact_Address].[ContactID] AS [ContactID],
[Contact_Address].[AddressID] AS [AddressID]
FROM [dbo].[Contact_Address] AS [Contact_Address]
</DefiningQuery>
</EntitySet>
<AssociationSet Name="FK_Address"
Association="ContactInformationModel.Store.FK_Address">
<End Role="Address" EntitySet="Address" />
<End Role="Contact_Address" EntitySet="Contact_Address" />
</AssociationSet>
<AssociationSet Name="FK_Contact"
Association="ContactInformationModel.Store.FK_Contact">
<End Role="Contact" EntitySet="Contact" />
<End Role="Contact_Address" EntitySet="Contact_Address" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Address">
<Key>
<PropertyRef Name="AddressID" />
</Key>
<Property Name="AddressID" Type="int" Nullable="false" />
<Property Name="StreetAddress" Type="nvarchar"
Nullable="false" MaxLength="50" />
<Property Name="City" Type="nvarchar"
Nullable="false" MaxLength="50" />
</EntityType>
<EntityType Name="Contact">
<Key>
<PropertyRef Name="ContactID" />
</Key>
<Property Name="ContactID" Type="int" Nullable="false" />
<Property Name="LastName" Type="nvarchar"
Nullable="false" MaxLength="50" />
<Property Name="FirstName" Type="nvarchar"
Nullable="false" MaxLength="50" />
</EntityType>
<EntityType Name="Contact_Address">
<Key>
<PropertyRef Name="ContactID" />
<PropertyRef Name="AddressID" />
</Key>
<Property Name="ContactID" Type="int" Nullable="false" />
<Property Name="AddressID" Type="int" Nullable="false" />
</EntityType>
<Association Name="FK_Address">
<End Role="Address"
Type="ContactInformationModel.Store.Address"
Multiplicity="1" />
<End Role="Contact_Address"
Type="ContactInformationModel.Store.Contact_Address"
Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="Address">
<PropertyRef Name="AddressID" />
</Principal>
<Dependent Role="Contact_Address">
<PropertyRef Name="AddressID" />
</Dependent>
</ReferentialConstraint>
</Association>
<Association Name="FK_Contact">
<End Role="Contact"
Type="ContactInformationModel.Store.Contact"
Multiplicity="1" />
<End Role="Contact_Address"
Type="ContactInformationModel.Store.Contact_Address"
Multiplicity="*" />
<ReferentialConstraint>
<Principal Role="Contact">
<PropertyRef Name="ContactID" />
</Principal>
<Dependent Role="Contact_Address">
<PropertyRef Name="ContactID" />
</Dependent>
</ReferentialConstraint>
</Association>
<Function Name="DeleteAddress"
Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
<Parameter Name="ContactID" Type="int" Mode="In" />
<Parameter Name="AddressID" Type="int" Mode="In" />
</Function>
<Function Name="SetAddress" Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion"
Schema="dbo">
<Parameter Name="ContactID" Type="int" Mode="In" />
<Parameter Name="AddressID" Type="int" Mode="In" />
</Function>
</Schema>
</edmx:StorageModels>
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema Namespace="ContactInformationModel" Alias="Self"
xmlns="https://schemas.microsoft.com/ado/2006/04/edm">
<EntityContainer Name="ContactInformationEntities">
<EntitySet Name="Addresses"
EntityType="ContactInformationModel.Address" />
<EntitySet Name="Contacts"
EntityType="ContactInformationModel.Contact" />
<AssociationSet Name="Contact_Address"
Association="ContactInformationModel.Contact_Address">
<End Role="Address" EntitySet="Addresses" />
<End Role="Contact" EntitySet="Contacts" />
</AssociationSet>
</EntityContainer>
<EntityType Name="Address">
<Key>
<PropertyRef Name="AddressID" />
</Key>
<Property Name="AddressID" Type="Int32" Nullable="false" />
<Property Name="StreetAddress" Type="String" Nullable="false"
MaxLength="50" Unicode="true" FixedLength="false" />
<Property Name="City" Type="String" Nullable="false"
MaxLength="50" Unicode="true" FixedLength="false" />
<NavigationProperty Name="Contact"
Relationship="ContactInformationModel.Contact_Address"
FromRole="Address" ToRole="Contact" />
</EntityType>
<EntityType Name="Contact">
<Key>
<PropertyRef Name="ContactID" />
</Key>
<Property Name="ContactID" Type="Int32" Nullable="false" />
<Property Name="LastName" Type="String" Nullable="false"
MaxLength="50" Unicode="true" FixedLength="false" />
<Property Name="FirstName" Type="String" Nullable="false"
MaxLength="50" Unicode="true" FixedLength="false" />
<NavigationProperty Name="Address"
Relationship="ContactInformationModel.Contact_Address"
FromRole="Contact" ToRole="Address" />
</EntityType>
<Association Name="Contact_Address">
<End Role="Address"
Type="ContactInformationModel.Address" Multiplicity="*" />
<End Role="Contact"
Type="ContactInformationModel.Contact" Multiplicity="*" />
</Association>
</Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S"
xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
<EntityContainerMapping StorageEntityContainer="dbo"
CdmEntityContainer="ContactInformationEntities">
<EntitySetMapping Name="Addresses">
<EntityTypeMapping
TypeName="IsTypeOf(ContactInformationModel.Address)">
<MappingFragment StoreEntitySet="Address">
<ScalarProperty Name="AddressID" ColumnName="AddressID" />
<ScalarProperty Name="StreetAddress"
ColumnName="StreetAddress" />
<ScalarProperty Name="City" ColumnName="City" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<EntitySetMapping Name="Contacts">
<EntityTypeMapping
TypeName="IsTypeOf(ContactInformationModel.Contact)">
<MappingFragment StoreEntitySet="Contact">
<ScalarProperty Name="ContactID" ColumnName="ContactID" />
<ScalarProperty Name="LastName" ColumnName="LastName" />
<ScalarProperty Name="FirstName" ColumnName="FirstName" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<AssociationSetMapping Name="Contact_Address"
TypeName="ContactInformationModel.Contact_Address"
StoreEntitySet="Contact_Address">
<EndProperty Name="Address">
<ScalarProperty Name="AddressID" ColumnName="AddressID" />
</EndProperty>
<EndProperty Name="Contact">
<ScalarProperty Name="ContactID" ColumnName="ContactID" />
</EndProperty>
<ModificationFunctionMapping>
<DeleteFunction
FunctionName="ContactInformationModel.Store.DeleteAddress">
<EndProperty Name="Address">
<ScalarProperty Name="AddressID"
ParameterName="AddressID"/>
</EndProperty>
<EndProperty Name="Contact">
<ScalarProperty Name="ContactID"
ParameterName="ContactID"/>
</EndProperty>
</DeleteFunction>
<InsertFunction
FunctionName="ContactInformationModel.Store.SetAddress">
<EndProperty Name="Address">
<ScalarProperty Name="AddressID"
ParameterName="AddressID"/>
</EndProperty>
<EndProperty Name="Contact">
<ScalarProperty Name="ContactID"
ParameterName="ContactID"/>
</EndProperty>
</InsertFunction>
</ModificationFunctionMapping>
</AssociationSetMapping>
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
<edmx:Designer xmlns="https://schemas.microsoft.com/ado/2007/06/edmx">
<edmx:Connection>
<DesignerInfoPropertySet>
<DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
</DesignerInfoPropertySet>
</edmx:Connection>
<edmx:Options>
<DesignerInfoPropertySet>
<DesignerProperty Name="ValidateOnBuild" Value="true" />
</DesignerInfoPropertySet>
</edmx:Options>
<edmx:Diagrams >
<Diagram Name="ContactInformation">
<EntityTypeShape EntityType="ContactInformationModel.Address" PointX="0.75" PointY="0.875" Width="1.5" Height="1.787985026041667" IsExpanded="true" />
<EntityTypeShape EntityType="ContactInformationModel.Contact" PointX="3" PointY="0.875" Width="1.5" Height="1.787985026041667" IsExpanded="true" />
<AssociationConnector Association="ContactInformationModel.Contact_Address" ManuallyRouted="false">
<ConnectorPoint PointX="2.25" PointY="1.7689925130208335" />
<ConnectorPoint PointX="3" PointY="1.7689925130208335" />
</AssociationConnector>
</Diagram>
</edmx:Diagrams>
</edmx:Designer>
See Also
Tasks
How to: Define a Model with a Stored Procedure (Entity Framework)
How to: Execute a Query Using a Stored Procedure (Entity Framework)
Concepts
Stored Procedure Support (Entity Framework)
ModificationFunctionMapping (AssociationSetMapping)
ModificationFunctionMapping (EntityTypeMapping)