Entity Framework 7 and Conditional Foreign Keys

Question

A legacy database I'm working with used a pattern like

CREATE TABLE [Document](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EntityTypeID] [int] NOT NULL,  -- Tells us if the document is for a item, person, company....
    [EntityID] [bigint] NOT NULL,   -- Is the primary key value from the item, person, company table
    [Url] [nvarchar](1024) NULL,
)

There is no FK defined in the document table since each record could be describing data related to a different parent table.

Example:

Document table

ID	EntityTypeID	EntityID	Url
1	1	123	...
2	1	123	...
3	2	123	...
4	2	123	...
5	3	567	...
6	3	456	...

Person table

ID	Name
123	Dave

Item table

ID	Name
123	Headphones

Company Table

ID	Name
567	Acme
456	Globlex

Say the EntityTypeIDs are defined as Person = 1, Item = 2, and Company = 3. Then

Document.ID 1 and 2 are Dave's documents
Document.ID 3 and 4 are documents about Headphones  
Document.ID 5 are documents for Acme
Document.ID 6 are documents for Globlex

My first thought was to create views for PersonDocument, ItemDocument and CompanyDocument and in OnModelCreating do something like

modelBuilder.Entity<PersonDocument>(entity => 
{
  ...
  entity.HasOne(d => d.Person).WithMany(p => p.PersonDocument).HasForeignKey(d => d.EntityId);
  ...
 }

That works for reading the data and populating my DTO classes but I don't know if I could update or create new Document records using that approach.

When I try to add a document I get

The entity type 'PersonDocument' is not mapped to a table, therefore the entities cannot be persisted to the database. Call ToTable

How do I handle this?

Answer 1

in my opinion that it is better for you to have a table documents and have tables that specify which document this is related to(PersonDocumet,CompanyDocumet,ItemDocument), so that you can have a control.

when you insert data ,step1 :insert to document then insert to related to other table

Dcoument: Id,Url

Person Dcoument:

DocumentId, PersonID

---

CompanyDocumet:

DocumentId, CompanyID

---

ItemDocument:

DocumentId, ItemID