Mapping di set di associazioni alle stored procedure (Entity Framework)

L'elemento ModificationFunctionMapping all'interno di un elemento AssociationSetMapping specifica le stored procedure che inseriscono ed eliminano istanze di un'associazione tra entità in Entity Framework. L'elemento ModificationFunctionMapping all'interno di un elemento AssociationSetMapping risulta utile durante il mapping di associazioni molti-a-molti in genere supportate da una tabella dei collegamenti nel database.

Quando si esegue il mapping di una relazione di chiave esterna, ad esempio una relazione tra SalesOrderDetail e SalesOrderHeader nel database di esempio AdventureWorks, utilizzare la tecnica di associazione AssociationEnd in un elemento ModificationFunctionMappingEntityTypeMapping. Per ulteriori informazioni, vedere Supporto delle stored procedure (Entity Framework).

L'elemento di livello superiore in un elemento AssociationSetMapping è l'elemento ModificationFunctionMapping. Gli elementi figlio DeleteFunction e InsertFunction descrivono le associazioni dei parametri per le entità finali dell'associazione.

L'attributo FunctionName per DeleteFunction e InsertFunction è, in ogni caso, il nome della stored procedure a cui viene fatto riferimento nel modello di archiviazione.

Nota
Non è disponibile alcun elemento UpdateFunction per i set di associazioni in quanto le associazioni possono essere create o eliminate, ma non modificate.

Le associazioni EndProperty dichiarate nel mapping della stored procedure di un set di associazioni assomigliano all'elemento AssociationEnd dichiarato in un elemento EntitySetMapping, ma richiedono meno contesto in quanto l'elemento padre indica l'elemento AssociationSet. Gli elementi figlio ScalarProperty descrivono le associazioni dei parametri per i valori chiave nelle entità finali dell'associazione e i parametri corrispondenti delle stored procedure.

Nell'esempio seguente viene eseguito il mapping delle funzioni di inserimento ed eliminazione che creano ed eliminano voci in una tabella dei collegamenti molti-a-molti che supporta l'associazione 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

Per creare il database utilizzato in questo esempio, eseguire lo script seguente:

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]

Per creare le stored procedure utilizzate in questo esempio, eseguire gli script seguenti:

Per creare la funzione utilizzata per creare un'istanza dell'associazione, utilizzare lo script seguente:

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)

Per creare la funzione utilizzata per eliminare un'istanza dell'associazione, utilizzare lo script seguente:

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

Lo schema completo con estensione edmx riportato di seguito è stato modificato manualmente per implementare AssociationSetMapping e ModificationFunctionMapping. Gli strumenti della finestra di progettazione di Entity Framework non supportano l'implementazione dell'elemento AssociationSetMapping di 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>

Vedere anche

Attività

Procedura: definire un modello con una stored procedure (Entity Framework)
Procedura: eseguire una query utilizzando una stored procedure (Entity Framework)

Concetti

Supporto delle stored procedure (Entity Framework)
ModificationFunctionMapping (AssociationSetMapping)
ModificationFunctionMapping (EntityTypeMapping)