Mapping Complex Type to Stored Procedures (Entity Framework)
The Entity Data Model (EDM) supports use of stored procedures for data modification to properties of the ComplexType. The example provided in this topic adds stored procedure support to the data model defined in the topic How to: Define a Model with Complex Type.
Storage Model
The stored procedures used in this example are specified in store schema definition language (SSDL). The Function element identifies the stored procedures accessible in the database. Stored procedures are specified for three modification functions used to create, update, and delete instances of the CAddress ComplexType.
<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="CustomerComplexAddress.Store"
Alias="Self" Provider="System.Data.SqlClient"
ProviderManifestToken="2005"
xmlns="https://schemas.microsoft.com/ado/2006/04/edm/ssdl">
<EntityContainer Name="dbo">
<EntitySet Name="SCustomer"
EntityType="CustomerComplexAddress.Store.SCustomer" />
</EntityContainer>
<EntityType Name="SCustomer">
<Key>
<PropertyRef Name="CustomerId" />
</Key>
<Property Name="CustomerId" Type="int" Nullable="false" />
<Property Name="CompanyName" Type="nvarchar" MaxLength="50"/>
<Property Name="ContactName" Type="nvarchar" MaxLength="50"/>
<Property Name="ContactTitle" Type="nvarchar" MaxLength="50"/>
<Property Name="Address" Type="nvarchar" MaxLength="50" />
<Property Name="City" Type="nvarchar" MaxLength="50" />
<Property Name="Region" Type="nvarchar" MaxLength="50" />
<Property Name="PostalCode" Type="nvarchar" MaxLength="50" />
<Property Name="Country" Type="nvarchar" MaxLength="50" />
<Property Name="Phone" Type="nvarchar" MaxLength="50" />
<Property Name="Fax" Type="nvarchar" MaxLength="50" />
</EntityType>
<Function Name="CreateCustomerComplexAddress"
Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion"
Schema="dbo">
<Parameter Name="CustomerId" Type="int" Mode="In" />
<Parameter Name="CompanyName" Type="nvarchar" Mode="In" />
<Parameter Name="ContactName" Type="nvarchar" Mode="In" />
<Parameter Name="ContactTitle" Type="nvarchar" Mode="In" />
<Parameter Name="Address" Type="nvarchar" Mode="In" />
<Parameter Name="City" Type="nvarchar" Mode="In" />
<Parameter Name="Region" Type="nvarchar" Mode="In" />
<Parameter Name="PostalCode" Type="nvarchar" Mode="In" />
<Parameter Name="Country" Type="nvarchar" Mode="In" />
<Parameter Name="Phone" Type="nvarchar" Mode="In" />
<Parameter Name="Fax" Type="nvarchar" Mode="In" />
</Function>
<Function Name="DeleteCustomerComplexAddress"
Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion"
Schema="dbo">
<Parameter Name="CustomerId" Type="int" Mode="In" />
</Function>
<Function Name="UpdateCustomerComplexAddress"
Aggregate="false" BuiltIn="false"
NiladicFunction="false" IsComposable="false"
ParameterTypeSemantics="AllowImplicitConversion"
Schema="dbo">
<Parameter Name="CustomerId" Type="int" Mode="In" />
<Parameter Name="Address" Type="nvarchar" Mode="In" />
<Parameter Name="City" Type="nvarchar" Mode="In" />
<Parameter Name="Region" Type="nvarchar" Mode="In" />
<Parameter Name="PostalCode" Type="nvarchar" Mode="In" />
<Parameter Name="Country" Type="nvarchar" Mode="In" />
<Parameter Name="Phone" Type="nvarchar" Mode="In" />
<Parameter Name="Fax" Type="nvarchar" Mode="In" />
</Function>
</Schema>
The database used in this example can be created by running the following script in SQL Server Management Studio.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CustomerComplexAddress')
DROP DATABASE [CustomerComplexAddress]
CREATE DATABASE [CustomerComplexAddress]
GO
USE [CustomerComplexAddress]
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].[SCustomer]')
AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SCustomer](
[CustomerId] [int] NOT NULL,
[CompanyName] [nvarchar](50) NULL,
[ContactName] [nvarchar](50) NULL,
[ContactTitle] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Region] [nvarchar](50) NULL,
[PostalCode] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
CONSTRAINT [PK_SCustomer] PRIMARY KEY CLUSTERED
(
[CustomerId] 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].[CreateCustomerComplexAddress]')
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[CreateCustomerComplexAddress]
@CustomerId int,
@CompanyName nvarchar(50),
@ContactName nvarchar(50),
@ContactTitle nvarchar(50),
@Address nvarchar(50),
@City nvarchar(50),
@Region nvarchar(50),
@PostalCode nvarchar(50),
@Country nvarchar(50),
@Phone nvarchar(50),
@Fax nvarchar(50)
AS
INSERT INTO [dbo].[SCustomer]
([CustomerId]
,[CompanyName]
,[ContactName]
,[Address]
,[City]
,[Region]
,[PostalCode]
,[Phone]
,[Fax])
VALUES
(@CustomerId,
@CompanyName,
@ContactName,
@Address,
@City,
@Region,
@PostalCode,
@Phone,
@Fax)
'
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].[UpdateCustomerComplexAddress]')
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[UpdateCustomerComplexAddress]
@CustomerId int,
@Address nvarchar(50),
@City nvarchar(50),
@Region nvarchar(50),
@PostalCode nvarchar(50),
@Country nvarchar(50),
@Phone nvarchar(50),
@Fax nvarchar(50)
AS
UPDATE [dbo].[SCustomer]
SET [Address] = @Address,
[City] = @City,
[Region] = @Region,
[PostalCode] = @PostalCode,
[Country] = @Country,
[Phone] = @Phone,
[Fax] = @Fax
WHERE CustomerId = @CustomerId'
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].[DeleteCustomerComplexAddress]')
AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[DeleteCustomerComplexAddress]
@CustomerId int
AS
UPDATE [dbo].[SCustomer]
SET [Address] = Null,
[City] = Null,
[Region] = Null,
[PostalCode] = Null,
[Country] = Null,
[Phone] = Null,
[Fax] = Null
WHERE CustomerId = @CustomerId
'
END
Conceptual Model
The CAddress ComplexType and the CCustomer EntityType that uses it as a property are defined in conceptual schema definition language (CSDL). Use the following schema with edmgen.exe to generate the object model for this example.
<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="CustomerComplexAddress"
Alias="Self"
xmlns="https://schemas.microsoft.com/ado/2006/04/edm">
<EntityContainer Name="CustomerComplexAddressContext">
<EntitySet Name="CCustomers"
EntityType="CustomerComplexAddress.CCustomer" />
</EntityContainer>
<EntityType Name="CCustomer">
<Key>
<PropertyRef Name="CustomerId" />
</Key>
<Property Name="CustomerId" Type="Int32" Nullable="false" />
<Property Name="CompanyName" Type="String" />
<Property Name="ContactName" Type="String" />
<Property Name="ContactTitle" Type="String" />
<Property Name="Address" Type="Self.CAddress"
Nullable="false" />
</EntityType>
<ComplexType Name="CAddress">
<Property Name="StreetAddress" Type="String" />
<Property Name="City" Type="String" />
<Property Name="Region" Type="String" />
<Property Name="PostalCode" Type="String" />
<Property Name="Country" Type="String" />
<Property Name="Phone" Type="String" />
<Property Name="Fax" Type="String" />
</ComplexType>
</Schema>
Mapping Specification
The ModificationFunctionElement that maps stored procedures to the CAddress ComplexType in the conceptual model is defined in mapping specification language (MSL). The following schema shows the mapping for create, update, and delete functions identified in the storage model.
<?xml version="1.0" encoding="utf-8"?>
<Mapping Space="C-S"
xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
<EntityContainerMapping StorageEntityContainer="dbo"
CdmEntityContainer="CustomerComplexAddressContext">
<EntitySetMapping Name="CCustomers">
<EntityTypeMapping
TypeName="CustomerComplexAddress.CCustomer">
<MappingFragment StoreEntitySet="SCustomer">
<ScalarProperty Name="CustomerId"
ColumnName="CustomerId" />
<ScalarProperty Name="CompanyName"
ColumnName="CompanyName" />
<ScalarProperty Name="ContactName"
ColumnName="ContactName" />
<ScalarProperty Name="ContactTitle"
ColumnName="ContactTitle" />
<ComplexProperty Name="Address"
TypeName="CustomerComplexAddress.CAddress">
<ScalarProperty Name="StreetAddress"
ColumnName="Address" />
<ScalarProperty Name="City"
ColumnName="City" />
<ScalarProperty Name="Region"
ColumnName="Region" />
<ScalarProperty Name="PostalCode"
ColumnName="PostalCode" />
<ScalarProperty Name="Country"
ColumnName="Country" />
<ScalarProperty Name="Phone"
ColumnName="Phone" />
<ScalarProperty Name="Fax"
ColumnName="Fax" />
</ComplexProperty>
</MappingFragment>
<ModificationFunctionMapping >
<InsertFunction
FunctionName="CustomerComplexAddress.Store.CreateCustomerComplexAddress">
<ScalarProperty Name="CustomerId"
ParameterName="CustomerId" Version="Current"/>
<ScalarProperty Name="CompanyName"
ParameterName="CompanyName"
Version="Current"/>
<ScalarProperty Name="ContactName"
ParameterName="ContactName"
Version="Current"/>
<ScalarProperty Name="ContactTitle"
ParameterName="ContactTitle"
Version="Current"/>
<ComplexProperty
TypeName="CustomerComplexAddress.CAddress"
Name="Address">
<ScalarProperty Name="StreetAddress"
ParameterName="Address"
Version="Current"/>
<ScalarProperty Name="City"
ParameterName="City"
Version="Current"/>
<ScalarProperty Name="Region"
ParameterName="Region"
Version="Current"/>
<ScalarProperty Name="PostalCode"
ParameterName="PostalCode"
Version="Current"/>
<ScalarProperty Name="Country"
ParameterName="Country"
Version="Current"/>
<ScalarProperty Name="Phone"
ParameterName="Phone"
Version="Current"/>
<ScalarProperty Name="Fax"
ParameterName="Fax"
Version="Current"/>
</ComplexProperty>
</InsertFunction>
<UpdateFunction
FunctionName="CustomerComplexAddress.Store.UpdateCustomerComplexAddress">
<ScalarProperty Name="CustomerId"
ParameterName="CustomerId"
Version="Current"/>
<ComplexProperty
TypeName="CustomerComplexAddress.CAddress"
Name="Address">
<ScalarProperty Name="StreetAddress"
ParameterName="Address" Version="Current"/>
<ScalarProperty Name="City"
ParameterName="City" Version="Current"/>
<ScalarProperty Name="Region"
ParameterName="Region"
Version="Current"/>
<ScalarProperty Name="PostalCode"
ParameterName="PostalCode" Version="Current"/>
<ScalarProperty Name="Country"
ParameterName="Country" Version="Current"/>
<ScalarProperty Name="Phone"
ParameterName="Phone" Version="Current"/>
<ScalarProperty Name="Fax"
ParameterName="Fax" Version="Current"/>
</ComplexProperty>
</UpdateFunction>
<DeleteFunction
FunctionName="CustomerComplexAddress.Store.DeleteCustomerComplexAddress" >
<ScalarProperty Name="CustomerId"
ParameterName="CustomerId" Version="Original"/>
</DeleteFunction>
</ModificationFunctionMapping>
</EntityTypeMapping>
</EntitySetMapping>
</EntityContainerMapping>
</Mapping>
To run application code that uses the stored procedures defined and mapped in this example, use the code supplied in the topic How to: Add and Modify Objects with Complex Types (Entity Framework). Stored procedures mapped by using the ModificationFunctionElement are called implicitly when using the data model implemented in this topic. No modifications are required to application code.
See Also
Tasks
How to: Define a Model with Complex Type (Entity Framework)