Function Mapping with Inherited Types

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:

FunctionMappingModel

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:

WrapperFunctions

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.

PersonMapping

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.

EmployeeMapping

And, finally, the Manager entity:

ManagerMapping

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s