CODEWITHSUNDEEP
c#entity-frameworkentity-framework-5
Shaul Behr
Shaul Behr

Reputation: 37993

Entity Framework: how to map multiple tables with different keys to one entity?

I have a DB structure like this:

create table Person (
  ID bigint identity not null,
  FirstName varchar(100),
  LastName varchar(100),
  -- etc... lot's of generic fields that apply to a person, e.g. phone, address
)

create table Teacher (
  ID bigint identity not null,
  PersonID bigint not null,
  EmploymentDate date,
  -- plus a bunch of other teacher-specific fields
)

create table Student (
  ID bigint identity not null,
  PersonID bigint not null,
  EnrollmentDate date,
  -- plus a bunch of student-specific fields
)

create table SystemUser (
  ID bigint identity not null,
  PersonID bigint not null,
  UserName varchar(50) not null,
  -- plus any user specific fields
)

The relationship between Person and all the other fields is 1 -> 0:1, and each of the "subclasses" of Person has a unique key on PersonID. A SystemUser may be the same person as a Teacher or a Student; in fact you could conceivably have a person who is all three.

Now, I want to make EF entities to represent Teacher, Student and SystemUser, each of which either actually inherits from Person (1st prize), or at least incorporates the Person fields in the class, implicitly mapping to both underlying tables.

I've found plenty of examples online where they share the same primary key, but none where they do not share the same primary key, i.e. PersonID on the derived tables maps to ID on the Person table.

How do you you do it?

Upvotes: 2

Views: 1152

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

in fact you could conceivably have a person who is all three.

If this is your requirement you must not map person together with other classes. You must still have separate Person class and separate PersonDetail class where PersonDetail can have derived Student, Teacher and SystemUser classes mapped with TPT inheritance. You must map the relationship between Person and PersonDetail as one-to-many to support that requirement.

One you map person with inheritance your model will have this properties

  • One Person will be Student, Teacher or SystemUser
  • If you will indeed have a real person who acts as more than one role, you will need multiple Person records in your database for every role - each with its unique primary key
  • The role of existing Person record could not change - that means Student will always be Student and it could not be changed for example to Teacher without first deleting the Person/Student and creating new Person/Teacher.

Upvotes: 1

Related Questions