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.

New Entity Framework designer bugs in SP1 beta

David DeWinter and I have come across a few new and annoying bugs since we installed the Visual Studio Service Pack 1 beta yesterday.  The first and most annoying is detailed in a forum post in the MSDN Entity Framework forum.  The Error List window now lists fake errors for all of our subtypes that are children of abstract entities with a message similar to “Entity types B, C, D are mapped to table A without discriminating conditions on all mappings to the table.”  The project still builds and the errors can be ignored.

Another bug we found today relates to result column bindings for mapped sprocs on subtypes.  We have the results from Insert sprocs mapped to properties on the entity, such the as the Id.  These bindings don’t show up at all in the Mapping Details window, even though they exist in the MSL in the .edmx file.  At first I thought the mapping wasn’t there, and tried to add one.  After deselecting that entity and reselecting it the Mapping Details window showed no result column bindings.  I opened the file in the XML editor and looked at the mapping for that insert sproc.  There were several duplicate result column bindings there from my attempts to add it with the designer.  We’ve reported this bug at Microsoft Connect.

The last problem we ran into is not actually a bug, but a change in behavior.  Dave created a thread about it at the Entity Framework forum.  We had created the following extension method relying on the EntityKey of an EntityReference:

        /// <summary>

        /// Loads the specified <see cref=”EntityReference”/> if it is not loaded

        /// and only if there is an actual reference to load.

        /// </summary>

        /// <param name=”entityReference”>The entity reference </param>

        public static void LoadIfNeeded(this EntityReference entityReference)

        {

            // The EntityKey will be null if there is no reference.

            if (!entityReference.IsLoaded && entityReference.EntityKey != null)

            {

                entityReference.Load();

            }

        }

We had a situation where an entity shares a 0..1 relationship with another entity.  Calling Load() when that relationship was null caused an unnecessary database hit, which was exaggerated when called in a loop on several entities.  We’ve since been able to remove the need for a LoadIfNeeded() method, but it is still nice to have an EntityKey on references for other uses.

We’ll likely run into a few more caveats with the new beta, but the fixes to previous bugs are always welcome.  Hopefully it won’t be too long of a wait before another release.  One thing that surprised me was how long it took to install the service pack, much longer than the install of the .NET Framework 3.5 SP1 beta.