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.

Partial sprocs?

Dave and I have written a T4 template to generate Entity Framework-friendly INSERT, UPDATE, and DELETE stored procedures.  I’ll detail the template in a future post.  I had the crazy idea of implementing something similar to C#’s partial methods in SQL, which I named “partial stored procedures.”  The reason behind the idea is the same reason partial methods exist: a code generator (a T4 template in our case) generates the same code every time, overwriting any changes someone might have made to the generated code.  The Entity Framework and LINQ to SQL designers create partial methods in the generated code to provide points where a developer can execute code, such as before and after a property changes.  It’s possible to do something similar when generating stored procedures, but the performance hit might be discouraging.  Here’s an example of what the code generator might create:

CREATE PROCEDURE [dbo].[Person_Insert]
    @name varchar(50)
AS
BEGIN
    IF OBJECT_ID(‘[dbo].[usp_BeforePerson_Insert]’) IS NOT NULL
        EXEC [dbo].[usp_BeforePerson_Insert] @name

    INSERT INTO [Test] VALUES (@name)
    SELECT SCOPE_IDENTITY()

    IF OBJECT_ID(‘[dbo].[usp_AfterPerson_Insert]’) IS NOT NULL
        EXEC [dbo].[usp_AfterPerson_Insert] @name
END

I did a small amount of testing while I played around with the idea.  Inserting 100,000 rows into the two-column table using a regular stored procedure took 31 seconds.  After adding the additional SQL, but without creating the “partial” stored procedures, 100,000 executions took 35 seconds to complete.  When I created the “before” stored procedure with a simple SELECT in it, the time rose to 54 seconds.  Although it’s an interesting idea, there isn’t really any need for something like partial methods in SQL.  The same thing can be accomplished by adding INSTEAD OF and AFTER triggers to the table.