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.