Table Per Hierarchy Inheritance with Column Discriminator and Associations used in Derived Entity Types

In this article I will show how to model a table per hierarchy, a bit more complex than I found in books or other articles. I'm sure this model is present in real lif,e but I really didn't have the luck to find an example on the web when I needed it most. The complexity of this model is given by the Foreign Keys ( the Associations) which need to be placed in the derived entities types and the fact the discriminator depends on more than one column. The first problem I could fix, but the next one, with the discriminator based on two or three nullable columns I couldn't do it, unless if I use a single discriminator column (ie ItemType or something).

The database is very simple. It is designed for an eshop which sells products, services and also has some nice packages, that combines products and services and offers them for good prices compared with if they would be bought alone. The products and services are very different in terms of how data describes them, so the decision was to store them in separate tables. An invoice will have totals, etc and its invoice lines. The invoice line table will link exclusively to a product or a service or a product/service in a package. So for sample if an invoice line has the ProductID set and the PackageID set, then it means the customer bought a package with that product. If an invoice line has only the ProductID set, then it means the customer bought a standalone product. Same for a service. An invoice line can't have both the ProductID and ServiceID set. I hope this structure is clear.

I'm starting with a new and empty Entity Model. As a first step I Update Model from Database and add only the InvoiceLine and Product tables. I'm check the “Pluralize or singularize generated object names” and “Include foreign key columns in model”.

The association from Product and InvoiceLine is 0..1 to many. Since the ProductID is null, makes sense: an invoice line could have or not a product. The next thing is to create a new EntityType called ProductLine, derived from InvoiceLine:
1.right-click the design surface, select Add->Entity, name it ProductLine and set the BaseType to InvoiceLine.
2.right-click the ProductLine, click Table Mapping and on Mapping Details, Add a Table or View and select InvoiceLine
3.cut and paste the ProductID from InvoiceLine to ProductLine
4.map in Mapping Details the ProductID column to ProductID value/property
5.right-click the ProductLine entity, Table Mapping, click “Add a condition” and add the conditions When ProductID is Not Null and PackageID is Null.
6.right-click ProductID property, click Properties and set Nullable to False. Since this derived entity is for Product there is no reason to let the Product is nullable.
7.Delete the 0..1 - * association between Product and InvoiceLine
8.right-click the design surface, add Association. Choose Product on the left side and ProductLine on the right side, so will see “Product can have * (Many) instances of ProductLine.”. Uncheck Add foreign key properties to 'ProductLine' Entity. Click OK.
9. double-click the new association. Choose Product as Principal, ProductID as Dependent Properties.
10. Save and Compile.



Before going further, one more thing. I can't create and save ProductLine because there is no InvoiceEntity added yet. So, right click the design surface, Update Model from Database, Add the Invoice table to the model. This is also a good test for Update Model from Database function which sometimes can broke previously made changes. See how I can use the entities:

var invoice = new Invoice();
var product = new Product {Name = "Product"};
var productLine = new ProductLine {Invoice = invoice, Product = product, Title = "Shoe Product",Discriminator="P"} ;
InvoiceLines.AddObject(productLine);
SaveChanges();

var query = from p in InvoiceLines.OfType<ProductLine>().Include("Product")
       select p;

query.Dump();

During this I encountered some problems. One of them was with the Association between the Product and the ProductLine, I was getting this error:

Problem in mapping fragments starting at lines 135, 145, 154:Foreign key constraint 'FK_InvoiceLine_product' from table InvoiceLine (ProductID) to table Product (ProductID):: Insufficient mapping: Foreign key must be mapped to some AssociationSet or EntitySets participating in a foreign key association on the conceptual side.”

This was because in the StorageModel there was present this Foreign Key, but in the conceptual model there was no Association either between the Product and InvoiceLine or between Product and newly created ProductLine. This association must have a ReferentialConstraint also.

Another error was:
“Multiplicity is not valid in Role 'Product' in relationship 'ProductProductLine'. Because all the properties in the Dependent Role are nullable, multiplicity of the Principal Role must be '0..1'.”
This error was because when I moved the ProductID property to ProductLine, I didn't set its Nullable property to false.

Other one was:
“Problem in mapping fragments starting at lines 153, 163:EntityTypes DB.InvoiceLine, DB.ProductLine are being mapped to the same rows in table InvoiceLine. Mapping conditions can be used to distinguish the rows that these types are mapped to.”

This is clear. The InvoiceLine and ProductLine entity types can't share the same rows in InvoiceLine table. This was fixed by adding the Condition ProductID is not null at the ProductLine EntityTypeMapping. I added also and the condition PackageID is null, because I don't want these rows, they belong to a package.

The next step is to create the ServiceLine, ProductPackageLine and ServicePackageLine entity types.
I start with ServiceLine because ServinceLine and ProductLine will be base types for ProductPackageLine and ServicePackageLine. For ServiceLine EntityType I will repeat the steps for ProductLine, after I will add the Service table in the model.

Some tests with LinqPad:

var invoice = Invoices.First();
var product = new Product {Name = "Product 2"};
var productLine = new ProductLine {Invoice = invoice, Product = product, Title = "Shoe Product 2",Discriminator="P"} ;
var service = new Service {Name = "Service 2"};
var serviceLine = new ServiceLine {Invoice = invoice, Service = service, Title = "Database Services",Discriminator="S"} ;
InvoiceLines.AddObject(productLine);
InvoiceLines.AddObject(serviceLine);

SaveChanges();

var queryP = from p in InvoiceLines.OfType<ProductLine>().Include("Product")
   select p;

queryP.Dump();

var queryS = from p in InvoiceLines.OfType<ServiceLine>().Include("Service")
   select p;

queryS.Dump();

The next challenge is to create ServicePackageLine and ProductPackageLine Entity types.
1. Update Model from Database, add Package
2. Created a new EntityType called ProductPackageLine, base type is ProductLine (not InvoiceLine, because I want to access the Product via ProductLine.Product)
3. Cut and paste the PackageID property from InvoiceLine to ProductPackageLine
and map the column PackageID to PackageID property.
4. Create the “Is Not Null” condition for PackageID in the ProductPackage Mappings
Obs: I saw this error:
“There is no property with name 'PackageID' defined in type referred by Role 'InvoiceLine'.” This is because I just deleted the property PackageID from InvoiceLine. Basically I need to “move” also the Association between InvoiceLine and Package to ProductPackageLine and Package.
5. I “moved” the association


It seems to me kind obviously that the condition PackageID is not null would suffice, but there is not like I expected:
“Problem in mapping fragments starting at lines 265, 273:Two entities with different keys are mapped to the same row. Ensure these two mapping fragments do not map two groups of entities with different keys to the same group of rows.”
Let's see the conditions:
ServiceLine : ServiceID is not null and PackageId is null
ProductLine : ProductID is not null and PackageID is null
ProductPackageLine via ProductLine: PackageID is not null
Here EF gets confused with the condition PackageID is not null and can't tell if the the rows described by an EntityType can mix with other rows in the set of another EntityType. In my opinion they don't mix, but I needed to made
the decision to add the discriminator column (This was present from the beginning of this article, but it could been added later via Update Model from Database).
The steps are:
1. delete all conditions from derived entity types.
2. add the new conditions Discriminator='P' at ProductLine, Discriminator='S' at ServiceLine and Discriminator='PP' at ServicePackageLine
At compilation I got the error:
“Problem in mapping fragments starting at lines 257, 265, 276, 283:Column InvoiceLine.Discriminator has no default value and is not nullable. A column value is required to store entity data.
An Entity with Key (PK) will not round-trip when:
(PK is in 'InvoiceLines' EntitySet AND Entity is type [DB.InvoiceLine])”
This was cleared by doing the InvoiceLine Entity Type abstract. At the next compilation I got the following error:
“Problem in mapping fragments starting at line 257:Condition member 'InvoiceLine.Discriminator' with a condition other than 'IsNull=False' is mapped. Either remove the condition on InvoiceLine.Discriminator or remove it from the mapping.”
Simply as it says I need to remove the Discriminator property from InvoiceLine, which did.

And the final model is:


Too bad I needed to change the table's structure for this model. I could also let the InvoiceLine entity like it was, but I would have to take extra care for wrong data, like having an item with a Product and Service set. Also maybe I'm doing something wrong with the Null Conditions. Again, the scenario is Is Null - Not Null, Not Null - Is Null, Both Not Null, but I had no success with it.

I'm new to EF, playing with it for about two months, I spent almost 6 hours with this model, but mainly because of the lack of experience. It was a good one, I didn't really paid so much attention to modelling until these days. I learned a bit about conditions, inheritance and how please the storage model with the conceptual model regarding to associations.