Entity Framework : Table per Concrete Type and unique IDs across tables

Question

I have a few tables that share only a few navigation properties and an ID. I think Table per Concrete type inheritance would be interesting here.. (?) It looks something like this :

Contact (Base, Abstract, not mapped)
- ContactID
- navigation properties to other tables (email, phone, ..)

Person : Contact (mapped to table Person with various properties + ContactID)
- various properties

Company : Contact (mapped to table Company with various properties + ContactID)
- various properties

Now for this to work, the primary key (contactID) should be unique across all tables. 2 options then:
- GUIDs (not a fan)
- an additional DB table generating identities (with just a ContactID field, deriving tables have FK), this would not be mapped in EF.

Is this setup doable ? Also, what will happen in the ObjectContext ? What kind of temporary key does EF generate before calling SaveChanges ? Will it be unique across objects ?

Thanks for any thoughts. mike.

Answer 1

(not enough space in the comments section)

I've been running some tests.
The thing is you're OK as long as you ONLY specify the subtype you're querying for (ex. 'Address' in your case).
But if you query for the base type (even if you don't need the subtypes info), ex. only ContactPossibility.ID, the generated SQL will UNION all subtype tables.
So querying your 'trackable' collection of ContactPossibilities can create a performance problem.

I tried to work around this by unmapping the base entity and split the inherited entities to their own table + the common table, basically transforming the TPT into TPC : this worked fine from a conceptual perspective (after a lot of edmx editing).
Until I realized this was stupid... :) Indeed in that case you will always need to Union all underlying tables to query for the common data...
(Though I'm not sure in the case described at the end of this post, didn't pursue to test it)

So I guess, since mostly I will need to query for a specific type (person, company, address, phone,..), it's gonna be OK for now and hoping MS will come with a fix in EF4.5.

So I'll have to be careful when querying, another interesting example :
Let's say you want to select a person and then query for his address, something like (tried to follow your naming) :
var person = from b in context.ContactEntities.OfType-Person-() where b.FirstName.StartsWith("X") select b;
var address = from a in context.ContactPossibilities.OfType-Address-() where **a.ContactEntity == person.FirstOrDefault()** select a;
this will produce a Union between all the tables of the Contact derived entities, and performance issues : generated SQL takes ContactPossibility table and joins to Address on ContactPossibilityID, then joins a union of all Contact derived tables joined with the base Contact table, before finally joining a filtered Person table.

However, consider the following alternative :
var person = from b in context.ContactEntities.OfType-Person-() where b.FirstName.StartsWith("X")<BR> select b;
var address = from a in context.ContactPossibilities.OfType-Address-() where **a.ContactID == person.FirstOrDefault().ID** select a;
This will work fine : generated SQL takes ContactPossibility table and joins to Address on ContactPossibilityID, and then joins the filtered Person table.

Mike.

Answer 2

We use a similiar construction with the folowing db design:

ContactEntity

• ID

ContactPossibility

• ID
• Position
• ContactTypeID
• ContactEntityID

Address

• ID (=PK and FK to ContactPossibility.ID)
• Street
• etc.

Telephone

• ID (=PK and FK to ContactPossibility.ID)
• Number
• etc.

Person

• ID (=PK and FK to ContactEntity.ID)
• FirstName
• etc.

Company

• ID (=PK and FK to ContactEntity.ID)
• Name
• etc.

This results in the entity model in two abstract classes: ContactEntity (CE) & ContactPossibility (CP) and multiple derived classes (Address=CP, Email=CP, Person=CE, Company=CE). The abstract and derived classes (rows in the db ;) share the same unique identifier, because we use an ID field in derived classes that's a foreign key to the primary key of the abstract class. And we use Guid's for this, because our software has the requirement to function properly off-line (not connected to the main database) and we have to deal smoothly with synchronisation issues. Also, what's the problem with Guid's?

Entity Framework does support this db / class design very good and we have a lot of pleasure from this design.

Is this setup doable ? Also, what will happen in the ObjectContext ? What kind of temporary key does EF generate before calling SaveChanges ? Will it be unique across objects ?

The proposed setup is very very doable! The ObjectContext acts fine and will insert, update and delete the right tables for derived classes without effort. Temporary keys? You don't need them if you use the pattern of an ID for derived classes that is both primary key and foreign key to the abstract class. And with Guid's you can be pretty sure that's unique across objetcs.

Furthermore: The foreignKey from CP to CE will provide every CE (Person, Company, User, etc.) with a trackable collection of ContactPossibilities. Which is real cool and handy.

Hope this helps...