TPH on relationship

I had an EF question yesterday. The DB has two tables:

  • a table Customers
    • CustomerId (PK)
    • Name
    • CustomerTypeId (FK to CustomerTypes.CustomerTypeId)
  • a table CustomerTypes
    • CustomerTypeId (PK)
    • Description

The guy wants to keep the relationship and to have TPH inheritance on Customers.CustomerTypeId.

So this means two mappings on Customers.CustomerTypeId which is forbidden. But we can do this with ssdl view:

<EntitySet Name=CustomersCustomerType EntityType=TestTHBModel.Store.CustomersCustomerType>

    <DefiningQuery>

        SELECT C.CustomerId, CT.CustomerTypeId

        FROM Customers AS C

        INNER JOIN CustomerTypes AS CT ON C.CustomerTypeId = CT.CustomerTypeId

    </DefiningQuery>

</EntitySet>

<EntityType Name=CustomersCustomerType>

    <Key>

        <PropertyRef Name=CustomerId />

    </Key>

    <Property Name=CustomerId Type=int Nullable=false />

    <Property Name=CustomerTypeId Type=int Nullable=true />

</EntityType>


Then, we can map it with the designer:

<edmx:Mappings>

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

        <EntityContainerMapping StorageEntityContainer=TestTHBModelStoreContainer CdmEntityContainer=TestTHBEntities>

            <EntitySetMapping Name=Customers>

                <EntityTypeMapping TypeName=IsTypeOf(TestTHBModel.Customer)>

                    <MappingFragment StoreEntitySet=Customers>

                        <ScalarProperty Name=CustomerId ColumnName=CustomerId />

                        <ScalarProperty Name=Name ColumnName=Name />

                    </MappingFragment>

                </EntityTypeMapping>

                <EntityTypeMapping TypeName=IsTypeOf(TestTHBModel.OldCustomer)>

                    <MappingFragment StoreEntitySet=CustomersCustomerType >

                        <ScalarProperty Name=CustomerId ColumnName=CustomerId />

                        <Condition ColumnName=CustomerTypeId Value=1 />

                    </MappingFragment>

                </EntityTypeMapping>

                <EntityTypeMapping TypeName=IsTypeOf(TestTHBModel.NewCustomer)>

                    <MappingFragment StoreEntitySet=CustomersCustomerType >

                        <ScalarProperty Name=CustomerId ColumnName=CustomerId />

                        <Condition ColumnName=CustomerTypeId Value=2 />

                    </MappingFragment>

                </EntityTypeMapping>

            </EntitySetMapping>

            <EntitySetMapping Name=CustomerTypes>

                <EntityTypeMapping TypeName=IsTypeOf(TestTHBModel.CustomerType)>

                    <MappingFragment StoreEntitySet=CustomerTypes>

                        <ScalarProperty Name=CustomerTypeId ColumnName=CustomerTypeId />

                        <ScalarProperty Name=Description ColumnName=Description />

                    </MappingFragment>

                </EntityTypeMapping>

            </EntitySetMapping>

            <AssociationSetMapping Name=FK_Customers_CustomerTypes TypeName=TestTHBModel.FK_Customers_CustomerTypes StoreEntitySet=Customers>

                <EndProperty Name=CustomerTypes>

                    <ScalarProperty Name=CustomerTypeId ColumnName=CustomerTypeId />

                </EndProperty>

                <EndProperty Name=Customers>

                    <ScalarProperty Name=CustomerId ColumnName=CustomerId />

                </EndProperty>

            </AssociationSetMapping>

        </EntityContainerMapping>

    </Mapping>

</edmx:Mappings>


This post shows you, as all the previous posts on SSDL view, the big interest of ssdl view: to make possible non-supported scenarii.

This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>