This post was inspired by a post on the Entity Framework forums asking for an example of mapping functions to a base type and its inherited types. I’m going to show two ways to go about it with a simple model. Note that this isn’t necessarily the best data model, it’s just a Table-Per-Hierarchy (TPH) example. We have a top-level Person type with an Employee subtype, which subsequently has a Manager subtype, defined in one table:
CREATE TABLE [dbo].[Person](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[PersonTypeId] [int] NOT NULL,
[EmployeeTypeId] [int] NULL,
[HireDate] [datetime] NULL,
[TeamName] [varchar](50) NULL,
[OfficeLocation] [varchar](50) NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
The Entity Model is defined below:
There are at least two ways to map the three entities to CUD stored procedures. We could create one procedure per table, or we could create one procedure per type. In this post I’ll show how to map the entities in the hierarchy to the same three stored procedures, which I generated using the T4 template David DeWinter and I developed. The Insert, Update, and Delete stored procedures are defined as follows:
CREATE PROCEDURE “dbo”.”zsp_Person_Insert”
(
@FirstName varchar(50),
@PersonTypeId int,
@EmployeeTypeId int,
@HireDate datetime,
@TeamName varchar(50),
@OfficeLocation varchar(50)
)
AS
BEGIN
INSERT INTO “dbo”.”Person”
(
“FirstName”,
“PersonTypeId”,
“EmployeeTypeId”,
“HireDate”,
“TeamName”,
“OfficeLocation”
)
VALUES
(
@FirstName,
@PersonTypeId,
@EmployeeTypeId,
@HireDate,
@TeamName,
@OfficeLocation
)
SELECT SCOPE_IDENTITY() AS PersonId
END
GO
CREATE PROCEDURE “dbo”.”zsp_Person_Update”
(
@PersonId int,
@FirstName varchar(50),
@PersonTypeId int,
@EmployeeTypeId int,
@HireDate datetime,
@TeamName varchar(50),
@OfficeLocation varchar(50)
)
AS
BEGIN
UPDATE “dbo”.”Person”
SET
“FirstName” = @FirstName,
“PersonTypeId” = @PersonTypeId,
“EmployeeTypeId” = @EmployeeTypeId,
“HireDate” = @HireDate,
“TeamName” = @TeamName,
“OfficeLocation” = @OfficeLocation
WHERE
“PersonId” = @PersonId
END
GO
CREATE PROCEDURE “dbo”.”zsp_Person_Delete”
(
@PersonId int
)
AS
BEGIN
DELETE FROM “dbo”.”Person”
WHERE
“PersonId” = @PersonId
END
GO
The trick in this case is to create some “wrapper” functions in the SSDL for the subtypes as Colin Meek suggested in this forum post. We don’t need to create functions for deleting, because the original delete stored procedure works fine. Here is the SSDL I ended up with:
<Function Name=“zsp_Person_Delete“ …>
<Parameter Name=“PersonId“ Type=“int“ Mode=“In“ />
</Function>
<Function Name=“zsp_Person_Insert“ …>
<Parameter Name=“FirstName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“PersonTypeId“ Type=“int“ Mode=“In“ />
<Parameter Name=“EmployeeTypeId“ Type=“int“ Mode=“In“ />
<Parameter Name=“HireDate“ Type=“datetime“ Mode=“In“ />
<Parameter Name=“TeamName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“OfficeLocation“ Type=“varchar“ Mode=“In“ />
</Function>
<Function Name=“zsp_Person_Update“ …>
<Parameter Name=“PersonId“ Type=“int“ Mode=“In“ />
<Parameter Name=“FirstName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“PersonTypeId“ Type=“int“ Mode=“In“ />
<Parameter Name=“EmployeeTypeId“ Type=“int“ Mode=“In“ />
<Parameter Name=“HireDate“ Type=“datetime“ Mode=“In“ />
<Parameter Name=“TeamName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“OfficeLocation“ Type=“varchar“ Mode=“In“ />
</Function>
<Function Name=“Employee_Insert“ …>
<CommandText>
<![CDATA[exec dbo.zsp_Person_Insert @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=1, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=null]]>
</CommandText>
<Parameter Name=“FirstName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“HireDate“ Type=“datetime“ Mode=“In“ />
<Parameter Name=“TeamName“ Type=“varchar“ Mode=“In“ />
</Function>
<Function Name=“Employee_Update“ …>
<CommandText>
<![CDATA[exec dbo.zsp_Person_Update @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=1, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=null]]>
</CommandText>
<Parameter Name=“PersonId“ Type=“int“ Mode=“In“ />
<Parameter Name=“FirstName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“HireDate“ Type=“datetime“ Mode=“In“ />
<Parameter Name=“TeamName“ Type=“varchar“ Mode=“In“ />
</Function>
<Function Name=“Manager_Insert“ …>
<CommandText>
<![CDATA[exec dbo.zsp_Person_Insert @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=2, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=@OfficeLocation]]>
</CommandText>
<Parameter Name=“FirstName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“HireDate“ Type=“datetime“ Mode=“In“ />
<Parameter Name=“TeamName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“OfficeLocation“ Type=“varchar“ Mode=“In“ />
</Function>
<Function Name=“Manager_Update“ …>
<CommandText>
<![CDATA[exec dbo.zsp_Person_Update @FirstName=@Firstname, @PersonTypeId=2, @EmployeeTypeId=2, @HireDate=@HireDate, @TeamName=@TeamName, @OfficeLocation=@OfficeLocation]]>
</CommandText>
<Parameter Name=“PersonId“ Type=“int“ Mode=“In“ />
<Parameter Name=“FirstName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“HireDate“ Type=“datetime“ Mode=“In“ />
<Parameter Name=“TeamName“ Type=“varchar“ Mode=“In“ />
<Parameter Name=“OfficeLocation“ Type=“varchar“ Mode=“In“ />
</Function>
These “fake” stored procedures call the real stored procedure and pass in constant values where needed, such as the columns used in the conditions of the subtypes. After creating the functions in the SSDL, we just need to map them to the entities using the designer. They now show up in the drop down menu in the Mapping Details window:
We map the real stored procedures to the base Person type and ignore the extra parameters, or we could have created “wrappers” for the Person entity as well.
The Employee type is mapped to the wrapper functions. The properties inherited from Person aren’t automagically mapped, but it’s easy enough to select them from the drop down.
And, finally, the Manager entity:
You might have noticed that the Employee and Manager insert function mappings don’t show any Result Column Bindings. I did add them, but the designer doesn’t show them. It’s a bug I described in my last post. The last quirk to remember when mapping these “wrapper” functions is that they will be deleted from the SSDL after running the Update Model from Database wizard. If this solution doesn’t seem attractive, there is another option. You can create stored procedures for each type and map those procedures to their respective entity. I won’t show how to do that here, but it should be fairly straightforward.
Special thanks to Dave for his original work setting up the function mappings for our large model.
