Table-per-hierarchy and inheritance implementation issue

Question

I am migrating an old ASP application to a modern .NET version, to cut down on development times we are looking at the .NET 4.0 Entity Framework. However, we seem to have hit a brick wall in our development with this issue.

Given is a small part of our database: A table OBJECT which contains a list of cars and their respective properties. We also have a table OBJECT_OPTIONS which contains, for a given car in OBJECT, a list of OPTIONS, ACCESSORIES and STANDARD EQUIPMENT. These three types all have the same fields and are therefor stored in the same table. The column ncopt_type is used to discriminate between the different lists. Possible values are: 'opt', 'acc' and 'sta'. The table OBJECT_OPTIONS links to OBJECT via ncopt_obj_id which represents a unique car (obj_id) in table OBJECT.

Our goal is to provide the OBJECT entity with 3 properties that link to the different OBJECT_OPTIONS lists: - property OPTIONS - property ACCESSORIES - property STANDARDEQUIPMENT

We've tried different tutorials and walkthroughs concerning the table-per-hierarchy via inheritance model but haven't succeeded in creating a buildable model.

Technically what we did was:

• Create entity OBJECT
• Create entity OBJECT_OPTIONS, make it abstract
• Add entities OPTION, ACCESSORY and STANDARD_EQUIP all using basetype OBJECT_OPTIONS
• Add conditions to all three tables on ncopt_type = '...'
• Add 3 navigational properties to OBJECT, all linking to one of the inherited entities: OPTIONS, ACCESSORIES and STANDAARD_EQUIPMENT

A bunch of errors shows up during this setup, but we end up with this one:

Error 3032: Problem in mapping fragments starting at lines 250, 286:EntityTypes NCO.Model.OPTION, NCO.Model.ACCESSOIRE, NCO.Model.STANDAARD_EQUIP are being mapped to the same rows in table OBJECT_OPTIES. Mapping conditions can be used to distinguish the rows that these types are mapped to.

There is a condition present on all three objects though.

I have found no solution to this problem and have spent way too much time on it already. We are currently using a workaround method but would love to get this fixed as this situation will present itself a few more times by the end of the project.

Any help appreciated, if you need more information, please drop me a comment or an email.

Answer 1

I'm not convinced you can do this 100% from within the EF designer, but you can do it like this:

1. Create entity OBJECT (I've renamed this 'Vehicle' for the example)
2. Create entity OBJECT_OPTIONS, make it abstract
3. Add entities OPTION, ACCESSORY and STANDARD_EQUIP all using basetype OBJECT_OPTIONS
4. Add conditions to all three tables on ncopt_type = '...'

Then add in the properties you want using a partial class for Vehicle:

using System.Collections.Generic;
using System.Linq;

public partial class Vehicle
{
    public IEnumerable<ACCESSORY> Accessories
    {
        get { return this.OBJECT_OPTIONS.OfType<ACCESSORY>(); }
    }

    public IEnumerable<OPTION> Options
    {
        get { return this.OBJECT_OPTIONS.OfType<OPTION>(); }
    }

    public IEnumerable<STANDARD_EQUIP> StandardEquipments
    {
        get { return this.OBJECT_OPTIONS.OfType<STANDARD_EQUIP>(); }
    }
}