Stored Procedures with Inheritance

Question

Say I have a model with three entity types: Person, Customer, and Employee. Customer and Employee both inherit from Person in the CSDL, and Person has a PersonType property which acts as a discriminator. Customer and Employee also expose different properties. (e.g. Customer has LastContactDate and Employee has HireDate.) This model is representative of one table--Person--in the SSDL; so it represents a Table-per-Hierarchy inheritance relationship.

My question is on mapping CUD stored procedures to these entities. Is it possible just to have a Person_Insert, Person_Update, and Person_Delete? The problem I run into is that I cannot map parameters that don't exist as part of the inheritance chain (such as @hireDate for a Customer or @lastContactDate for an Employee). I also cannot pass in a value for @personType because it is the discriminator column (and thus not explicitly mapped in the CSDL).

Is there any workaround for this which will allow me to specify a value (such as the string "Employee" or <NULL>) for a parameter instead of a property, or will I have to create different sets of sprocs for each derived entity type (e.g. Customer_Insert, Customer_Update, Customer_Delete, Employee_Insert, Employee_Update, or Employee_Delete)?

Answer 1

You can map each type to separate stored procedures, but there is no direct support for reusing the same sprocs for each type. As a workaround, you can define sproc "wrappers" in the SSDL for each type with constants for the 'extra' parameters, e.g.:

 

Code Snippet

<Function Name="InsertPerson" ...>

<CommandText>

   exec sp_insert_person @pid = @pid, ..., @person_type='person', @customer_field=null

</CommandText>

...

</Function>

 

 

We are considering more seamless support for this pattern in the next release by allowing parameters to be mapped to constants in the mapping.

 

Thanks,

-Colin

Answer 2

Much appreciated, Colin! I was dreading having to create separate sets of sprocs for each entity type in each inheritance hierarchy.

I will definitely enjoy the experience more when we can map parameters to constants, though.

David

Answer 3

Thank you for that answer, it just fits (and fixes) my problem .

The more I use EF, and the less I can wait the next release.
I also strongly hope that it won't break my actual code, since there are many times where we need to edit directly the xml lines.