Compartir a través de

Asignar conjuntos de asociaciones a procedimientos almacenados (Entity Framework)

  • Artículo

El elemento ModificationFunctionMapping dentro de una AssociationSetMapping especifica los procedimientos almacenados que insertan y eliminan instancias de una asociación entre las entidades dentro de Entity Framework. El elemento ModificationFunctionMapping dentro de AssociationSetMapping es útil al asignar las asociaciones varios a varios que suelen admitirse en una tabla de vínculos en la base de datos.

Al asignar una relación de clave externa, como la relación entre SalesOrderDetail y SalesOrderHeader en la base de datos de ejemplo de AdventureWorks, utilice la técnica de enlace AssociationEnd en un elemento ModificationFunctionMappingEntityTypeMapping. Para obtener más información, vea Compatibilidad con los procedimientos almacenados (Entity Framework).

El elemento de nivel superior en AssociationSetMapping es ModificationFunctionMapping. Los elementos secundarios InsertFunction y DeleteFunction describen los enlaces de parámetro para los extremos de la asociación.

El atributo FunctionName para DeleteFunction e InsertFunction es, en cada caso, el nombre del procedimiento almacenado al que se hace referencia en el modelo de almacenamiento.

Nota

No hay ningún elemento UpdateFunction para los conjuntos de asociación porque las asociaciones no se pueden modificar, solo se pueden crear o eliminar.

Los enlaces de EndProperty declarados en una asignación de procedimientos almacenados de un conjunto de asociaciones se parecen el elemento AssociationEnd declarado en EntitySetMapping, pero requieren menos contexto porque el elemento primario indica el AssociationSet. Los elementos secundarios ScalarProperty describen los enlaces de parámetros para los valores de clave en los extremos de la asociación y los parámetros correspondientes de procedimientos almacenados.

En el ejemplo siguiente se asignan las funciones de inserción y eliminación que crean y eliminan las entradas en una tabla de vínculos de varios a varios que admite la asociación Contact_Address.

  <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

Para crear la base de datos de este ejemplo, ejecute el script siguiente.

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]

Para crear los procedimientos almacenados que se usan en este ejemplo, ejecute los scripts siguientes.

Utilice el script siguiente para crear la función que se utiliza para crear una instancia de la asociación:

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)

Utilice el script siguiente para crear la función que se utiliza para eliminar una instancia de la asociación:

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

El esquema .edmx completo, que se muestra a continuación, se ha modificado manualmente para implementar AssociationSetMapping y ModificationFunctionMapping. Las herramientas del diseñador de Entity Framework no admiten la implementación de AssociationSetMapping de 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>

Vea también

Tareas

Cómo definir un modelo con un procedimiento almacenado (Entity Framework)
Cómo ejecutar una consulta con un procedimiento almacenado (Entity Framework)

Conceptos

Compatibilidad con los procedimientos almacenados (Entity Framework)
ModificationFunctionMapping (AssociationSetMapping)
ModificationFunctionMapping (EntityTypeMapping)