Correct way to create a foreign key based on type, in SQL Server and Entity Framework Core

Question

I use Entity Framework Core and SQL Server in my application. I am very familiar with foreign keys but I would like to learn the correct way to give a foreign key to an entity which is based on a type.

For example:

Parent class AppUser:

CREATE TABLE AppUser
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    Name VARCHAR(255) NOT NULL,
    UserTypeId SMALLINT NOT NULL --  !This is the important part for me! if 0 = Student if 1 = Teacher
);

CREATE TABLE Student
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    AppUserId INT NOT NULL,

    CONSTRAINT FK_Student_AppUser FOREIGN KEY (AppUserId)
    REFERENCES AppUser(Id)
);

CREATE TABLE Teacher
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    AppUserId INT NOT NULL,

    CONSTRAINT FK_Student_AppUser FOREIGN KEY (AppUserId)
    REFERENCES AppUser(Id)
);

My question: is there a better way to have this relationship. Because in Entity Framework, when I try to get a user, I always need to check for the TypeId and have an if else block for the type, then make the query on the table student or teacher. Any advice would be great. Thanks!

Can you post some code showing what you're trying to achieve? Your UserTypeId column- what is its purpose? — MultiValidation
@PlamenYordanov For example when I try to update the student, I post the AppUser Id to the .net web api. Then, i have to get the AppUser with that Id. then I should make an if block such as: if(user.TypeId == 0) // update student. I directly want to get the student object without writing an if block — Halil Bulent Orhon
Well, there must be a condition that is met before updating a particular user, you have to identify the object in some way. How are you getting your user? — MultiValidation

LouraQ LouraQ · Accepted Answer · 2020-09-21T08:07:56

According to your table structures, if you want to get student through AppUser Id, you can directly use this code :

 var data = _context.Student.Include(x => x.AppUser).Where(x => x.AppUserId == userId).FirstOrDefault();

Next, you only need to distinguish the parameter of data .

If data is null, it represents that the Id of AppUser is not a student but a teacher(which means the UserTypeId field value is 1), otherwise, you can update the student fields you want.

           if (data != null)
            {
               //update student
            }

Correct way to create a foreign key based on type, in SQL Server and Entity Framework Core

1 Answers