ParentId and Parent type in Entity Framework

Question

I have many entities (Customers, prospects,...)

I have one entity called Notes, which can handle notes for Customers,prospects... The Notes table is then: Id Parent_Id Parent_Type Note

How to handle this with Entity Framework ?

Thanks Joghn

Answer 1

Don't do this.

If you're absolutely sure that all notes are the same abstract data type, even though they can be owned/used by different datatypes, build subtypes of note for each owner/user types.

This does four things: it allows you an escape hatch if requirement creep makes, e.g., customer notes end up being different than prospect notes, keeps you (or the Entity Framework ORM) from having to write ugly SQL, allows you to use normal referential integrity constraints in the database, and allows you to use decent type constraints in .Net.

Basically:

In the database:

create table note ( 
  id int synthetic key of some sort, 
  notetext text 
);

create table customer_note(  
  id int synthetic key of some sort, 
  customer_id int not null references customer(id), 
  note_id not null references note(id)
  -- add requirement creep customer note attribute data here
);

In .net:

void Customer::addNote( CustomerNote& note ) { // typing prevents accidentally adding a prospect note to a Customer ... }

(Only one 'problem" here: when a Prospect turns into a Customer, you'll have a converting constructor that reifies that change, which will ultimately involve deleting rows from table prospect_note and inserting them into customer_note. The ORM will do this the inefficient way, cascading the delete, but you'll be able to override that and do it efficiently if you need to, by writing a stored proc or the like.)