Table per type (subclass) inheritance

Question

Hello!

First of all I would like to congratulate you guys for the great job.

Having this database configuration, how can I create a table per type (subclass) mapping without discriminators?

Employee and Customer "IS A" Person, and Person have a FK (SomeTableId).
Employee and Customer entities inherits from Person.

I've looked at another post but don't fit with my needs.

CREATE TABLE Persons (
    PersonId INT NOT NULL,
    SomeTableId INT NOT NULL,
    [Name] NVARCHAR(50) NOT NULL,
)

CREATE TABLE Employees (
    PersonId INT PRIMARY KEY NOT NULL,
    Salary MONEY NOT NULL DEFAULT ((0.00))
)

CREATE TABLE Customers (
   PersonId INT PRIMARY KEY NOT NULL,
    Title NVARCHAR(50) NULL
)

CREATE TABLE SomeTable (
    SomeTableId INT PRIMARY KEY NOT NULL,
    [Value] NVARCHAR(50) NOT NULL
)

GO

I don't have a clue how I can do it. I have this configuration in msl.

<cdm:EntitySetMapping cdm:Name="Employees">
    <cdm:EntityTypeMapping cdm:TypeName="Model.Employee">

        <cdm:TableMappingFragment cdm:TableName="Persons">
            <cdm:ScalarProperty cdm:Name="PersonId" cdm:ColumnName="PersonId" />
            <cdm:ScalarProperty cdm:Name="Name" cdm:ColumnName="Name" />
        </cdm:TableMappingFragment>

        <cdm:TableMappingFragment cdm:TableName="Employees">
            <cdm:ScalarProperty cdm:Name="PersonId" cdm:ColumnName="PersonId" />
            <cdm:ScalarProperty cdm:Name="Salary" cdm:ColumnName="Salary" />
        </cdm:TableMappingFragment>

    </cdm:EntityTypeMapping>
</cdm:EntitySetMapping>

<cdm:EntitySetMapping cdm:Name="Customers">
    <cdm:EntityTypeMapping cdm:TypeName="Model.Customer">

        <cdm:TableMappingFragment cdm:TableName="Persons">
            <cdm:ScalarProperty cdm:Name="PersonId" cdm:ColumnName="PersonId" />
            <cdm:ScalarProperty cdm:Name="Name" cdm:ColumnName="Name" />
        </cdm:TableMappingFragment>

        <cdm:TableMappingFragment cdm:TableName="Customers">
            <cdm:ScalarProperty cdm:Name="PersonId" cdm:ColumnName="PersonId" />
            <cdm:ScalarProperty cdm:Name="Title" cdm:ColumnName="Title" />
        </cdm:TableMappingFragment>

    </cdm:EntityTypeMapping>
</cdm:EntitySetMapping>

If mapping only for Employees everything works, but when I try to map Customers, I get a error message saying that the mapping is repeated.

Sorry for my english, but I can guarantee that it's more easy to understand than Portuguese. ;)

Thanks.

Answer 1

In the above example, when you map the Employees entity set to the Persons table, you are saying:
"All records in the Persons table correspond to the entities in the Employees entity set"

When you map the Customers entity set to the Persons table also, you are saying:
"All records in the Persons table correspond to the entities Customers entity set"

That is, the records in the table go to both entity sets - this is not allowed since the values can go out of sync in the model space, e.g., the Name value for a record may be "foo" in the Customers set and it might be "bar" in the Employees set - which one should be stored in the table? Hence, this mapping is disallowed.

Answer 2

Hi Atul,

Thanks for the answer, but I still not knowing how to do the TPT mapping.
I did't find any example.

In fact, the Employees and Customers entities have values stored in both tables. Persons is the base table and is related with the derived tables through a PK (PersonId). The tables represents the objects faithfully. When a Customer is inserted, is added one row to Persons table with PersonId, CountryId and Name, and one row to table Customer with the same PersonId of Persons table and the Customer Title. The Customer entity have values from a joined table of Persons and Customers, I guess.

How can I represent polymorphism without using discriminators?
Where can I find more documentation for inheritance mapping?
Can you show me how create a mapping for this configuration?


csdl:
<Schema Namespace="testModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">

    <EntityType Name="Country" Key="CountryId">
        <Property Name="CountryId" Type="Int32" Nullable="false" />
        <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" Collation="" />
    </EntityType>
    <EntityType Name="Customer" BaseType="Self.Person">
        <Property Name="Title" Type="String" Nullable="true" MaxLength="50" Unicode="true" Collation="" />
    </EntityType>
    <EntityType Name="Employee" BaseType="Self.Person">
        <Property Name="Salary" Type="Decimal" Nullable="false" Precision="19" Scale="4" />
    </EntityType>
    <EntityType Name="Person" Key="PersonId">
        <Property Name="PersonId" Type="Int32" Nullable="false" />
        <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" Collation="" />
        <NavigationProperty Name="Country" FromRole="Person" ToRole="Country" Relationship="Self.FK_Persons_Countries" />
    </EntityType>
    <Association Name="FK_Persons_Countries">
        <End Role="Country" Type="Self.Country" Multiplicity="1" />
        <End Role="Person" Type="Self.Person" Multiplicity="0..*" />
    </Association>
    <EntityContainer Name="Example">
        <EntitySet Name="Country" EntityType="Self.Country" />
        <EntitySet Name="Customers" EntityType="Self.Customer" />
        <EntitySet Name="Employees" EntityType="Self.Employee" />
        <EntitySet Name="Persons" EntityType="Self.Person" />
        <AssociationSet Name="PersonsCountries" Association="Self.FK_Persons_Countries">
            <End EntitySet="Country" Role="Country" />
            <End EntitySet="Persons" Role="Person" />
        </AssociationSet>
    </EntityContainer>
</Schema>

ssdl:

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="testTarget" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
    <EntityType Name="Countries" Key="CountriesId">
        <Property Name="CountriesId" Type="int" Nullable="false" />
        <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
    </EntityType>
    <EntityContainer Name="dbo">
        <EntitySet Name="Countries" EntityType="testTarget.Countries" />
        <EntitySet Name="Customers" EntityType="testTarget.Customers" />
        <EntitySet Name="Employees" EntityType="testTarget.Employees" />
        <EntitySet Name="Persons" EntityType="testTarget.Persons" />
        <AssociationSet Name="FK_Persons_Countries" Association="testTarget.FK_Persons_Countries">
            <End Role="Countries" EntitySet="Countries" />
            <End Role="Persons" EntitySet="Persons" />
        </AssociationSet>
    </EntityContainer>
    <EntityType Name="Customers" Key="PersonId">
        <Property Name="PersonId" Type="int" Nullable="false" />
        <Property Name="Title" Type="nvarchar" MaxLength="50" />
    </EntityType>
    <EntityType Name="Employees" Key="PersonId">
        <Property Name="PersonId" Type="int" Nullable="false" />
        <Property Name="Salary" Type="money" Nullable="false" />
    </EntityType>
    <EntityType Name="Persons" Key="PersonId">
        <Property Name="PersonId" Type="int" Nullable="false" />
        <Property Name="CountriesId" Type="int" Nullable="false" />
        <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" />
    </EntityType>

    <Association Name="FK_Persons_Countries">
        <End Role="Countries" Type="testTarget.Countries" Multiplicity="1" />
        <End Role="Persons" Type="testTarget.Persons" Multiplicity="0..*" />
        <ReferentialConstraint FromRole="Countries" ToRole="Persons" FromProperty="CountriesId" ToProperty="CountriesId" />
    </Association>
</Schema>

I know that is a really dummy example but I'm developing a more complex application, and if this type of mapping it's not allowed I will have to change my entire database.

Thanks again. ;)

Answer 3

Here is an example of TPT mapping or vertical partitioning. It is a bit simpler than your example. Also, you may have to change the container names in the MSL to get it to work with your bits.

 

<Schema Namespace="Test.InheritanceVerticalPartitioning.Model" Alias="Self" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" xmlns:edm="http://schemas.microsoft.com/ado/2006/04/edm" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">

  <EntityContainer Name="Test_InheritanceVerticalPartitioning_Model_Northwind">

    <EntitySet Name="CPerson1" EntityType="Self.CPerson" />

  </EntityContainer>

  <EntityType Name="CPerson" Key="PersonId">

    <Property Name="PersonId" Type="Int32" Nullable="false" />

    <Property Name="Name" Type="String" MaxLength="1024" />

    <Property Name="Title" Type="String" MaxLength="1024" />

  </EntityType>

  <EntityType Name="CCustomer" BaseType="Self.CPerson">

    <Property Name="NumYears" Type="Int32" />

  </EntityType>

</Schema>

 

<Schema Namespace="Test.InheritanceVerticalPartitioning.Target" Alias="Self" xmlns:edm="http://schemas.microsoft.com/ado/2006/04/edm/ssdl" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

  <EntityContainer Name="Test_InheritanceVerticalPartitioning_Target_Northwind">

    <EntitySet Name="SPerson1" EntityType="Self.SPerson" Schema="Northwind" Table="SPerson1" />

    <EntitySet Name="SCustomer1" EntityType="Self.SCustomer" Schema="Northwind" Table="SCustomer1" />

  </EntityContainer>

  <EntityType Name="SPerson" Key="PersonID">

    <Property Name="PersonID" Type="int" Nullable="false" />

    <Property Name="FirstName" Type="nvarchar" MaxLength="1024" />

    <Property Name="Title" Type="nvarchar" MaxLength="1024" />

  </EntityType>

  <EntityType Name="SCustomer" Key="CustomerID">

    <Property Name="CustomerID" Type="int" Nullable="false" />

    <Property Name="NumYears" Type="int" />

  </EntityType>

</Schema>

 

 

<Mapping xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS" xmlns:cdm="urn:schemas-microsoft-com:windows:storage:mapping:CS" cdm:Space="C-S">

  <Alias cdm:Key="CNorthwind" cdm:Value="Test.InheritanceVerticalPartitioning.Model" />

  <Alias cdm:Key="SNorthwind" cdm:Value="Test.InheritanceVerticalPartitioning.Target" />

  <EntityContainerMapping cdm:CdmEntityContainer="Test_InheritanceVerticalPartitioning_Model_Northwind" cdm:StorageEntityContainer="Test_InheritanceVerticalPartitioning_Target_Northwind">

    <EntitySetMapping cdm:Name="CPerson1">

      <EntityTypeMapping cdm:TypeName="IsTypeOf(CNorthwind.CPerson)">

        <TableMappingFragment cdm:TableName="SPerson1">

          <ScalarProperty cdm:Name="PersonId" cdm:ColumnName="PersonID" />

          <ScalarProperty cdm:Name="Name" cdm:ColumnName="FirstName" />

          <ScalarProperty cdm:Name="Title" cdm:ColumnName="Title" />

        </TableMappingFragment>

      </EntityTypeMapping>

      <EntityTypeMapping cdm:TypeName="CNorthwind.CCustomer">

        <TableMappingFragment cdm:TableName="SCustomer1">

          <ScalarProperty cdm:Name="PersonId" cdm:ColumnName="CustomerID" />

          <ScalarProperty cdm:Name="NumYears" cdm:ColumnName="NumYears" />

        </TableMappingFragment>

      </EntityTypeMapping>

    </EntitySetMapping>

  </EntityContainerMapping>

</Mapping>

 

Answer 4

Hi Atul,

Thanks, your example helped me a lot.

Just two more questions:

The insertion fails when I relate the tables with one-to-one relationship to keep referential integrity.
This is a expected behavior?

Is there some way to create a CCustomers EntitySet to query against with Linq? I've tried to do this, but I get an exception saying "Invalid object name 'Container.CCustomers'."

By the way, the reduction in the code size is impressive. The code necessary to deal with simple crud operations in a table with 30 attributes fell of 400 (sp, dal, and business) to only 3 lines (not counting the mapping).

Tony

Answer 5

Hi Tony,

Could you give details on the failure you're seeing on insert? No failure is expected provided you've indicated all foreign key constraints in the SSDL file.

Re. the LINQ query, there is no way of creating new entity sets at runtime (they must be declared in the CSDL), but you can create a root query returning only entities of a certain type. The following example illustrates the pattern:

        ObjectQuery<Person> personsEntitySet = context.Persons;

        IQueryable<Customer> typeFilter = personsEntitySet.Where(

            p => p is Customer).Select(

            p => p as Customer);

In the final release of the Entity Framework, we will also support a simpler pattern using the OfType operator:

        IQueryable<Customer> ofTypeQuery = personsEntitySet.OfType<Customer>(); 

Let us know if you have any other questions,

Thanks,
-Colin

Answer 6

Hi Colin,

Using the exactly SSDL, SSDL and MSL posted by Atul, when I insert using this code:

Using db As New TPT
       Dim p As CPerson = New CCustomer(21)
       db.CPersons.Context.AddObject(p)
       db.SaveChanges()
       Dim e = (From n In db.CPersons _
               Where n.PersonID = 21 _
               Select n).ToSequence().First()
       db.DeleteObject(e)
       db.SaveChanges()
       For Each p In db.CPersons.OfType(Of CCustomer)()
               Console.WriteLine(p.PersonID)
       Next
End Using

I get these Exceptions:
An error occurred in the store while updating the given entries. See the InnerException for details.
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_SCustomer_SPerson'. The conflict occurred in database 'test1', table 'SPerson', column 'PersonID'.

I'm using SQL2K.

Between SPerson and SCustomer tables have a one-to-one relationship with "Cascade Delete" enabled.

Deleting is OK.

The filter OfType works fine.

I was not trying to create a new Entity Set at runtime, but I asked if is possible to define a Entity Set of derived type in CSDL EntityContainer like this:

<EntityContainer Name="TPT">
  <EntitySet Name="CPersons" EntityType="Self.CPerson" />
  <EntitySet Name="CCustomers" EntityType="Self.CCustomer" />
</EntityContainer>

I don't know how to create an association constraint when a type derives from another. I think if you define that a type inherits or derives from another, you don't must explicitate the association between they, this must be transparent.

If was necessary I can post the entire mapping configuration and db scripts.

Please tell me how I do these associations, and what I'm doing wrong.

Thanks,

Tony