Entity Framework Core Inheritance TPT (Data sharing)

[Diaz15h]

A person can be both a student and an employee in the same organization I made a TPT diagram.
I have two problems :

• To add an employee who is already a student I must use raw SQL (or vice versa)

• If I do the employee reader I have to create a new DbContext to do the student reader. If not I will have a CASTE error

Entity code

[Index( nameof(FirstName), nameof(LastName), nameof(BirthDate) ,IsUnique = true)]
public class Person
{
    [Key]public int BusinessEntityId { get; set; }
    [Required] [StringLength(50)] public string FirstName { get; set; }
    [Required] [StringLength(50)] public string LastName { get; set; }        
    [Required] [DataType(DataType.Date)] public DateTime BirthDate { get; set; }        
    public Guid Rowguid { get; set; }
    [Column(TypeName = "datetime")] public DateTime ModifiedDate { get; set; }        
    [ForeignKey(nameof(BusinessEntityId))] [InverseProperty("Person")] public BusinessEntity BusinessEntity { get; set; }

    public override string ToString() => $"First Name : {FirstName} -- Last Name : {LastName}  -- Birth Date : {BirthDate}";
}

public class PersonConfiguration : IEntityTypeConfiguration<Person>
{
    public void Configure(EntityTypeBuilder<Person> builder)
    {
        builder.HasIndex(x => x.Rowguid).IsUnique();

        builder.Property(p => p.Rowguid).HasDefaultValueSql("(newid())");
        builder.Property(x => x.ModifiedDate).HasDefaultValueSql("(getdate())");
    }
}

public class Student : Person
{        
    public Guid RowguidStudent { get; set; }
    [Column(TypeName = "datetime")]  public DateTime ModifiedDateStudent { get; set; }
}
public class StudentConfiguration : IEntityTypeConfiguration<Student>
{
    public void Configure(EntityTypeBuilder<Student> builder)
    {
        builder.ToTable(nameof(Student).Pluralize());

        builder.HasIndex(x => x.RowguidStudent).IsUnique();
        builder.Property(p => p.RowguidStudent).HasDefaultValueSql("(newid())");

        builder.Property(x => x.ModifiedDateStudent).HasDefaultValueSql("(getdate())");
    }
}

public class Employee : Person
{ 
    public  Guid RowguidEmployee { get; set; }
    [Column(TypeName = "datetime")] public   DateTime ModifiedDateEmplyee { get; set; }
}

public class EmployeeConfiguration : IEntityTypeConfiguration<Employee>
{
    public void Configure(EntityTypeBuilder<Employee> builder)
    {
        builder.ToTable(nameof(Employee).Pluralize());

        builder.HasIndex(x => x.RowguidEmployee).IsUnique();
        builder.Property(p => p.RowguidEmployee).HasDefaultValueSql("(newid())");
        builder.Property(x => x.ModifiedDateEmplyee).HasDefaultValueSql("(getdate())");
    }
}

public class BusinessEntity
{
    public int BusinessEntityId { get; set; }
    public Guid Rowguid { get; set; }
    [Column(TypeName = "datetime")] public DateTime ModifiedDate { get; set; }
    [InverseProperty("BusinessEntity")] public virtual Person Person { get; set; }
}

public class BusinessEntityConfiguration : IEntityTypeConfiguration<BusinessEntity>
{
    public void Configure(EntityTypeBuilder<BusinessEntity> builder)
    {
        builder.HasIndex(x => x.Rowguid).IsUnique();
        builder.Property(p => p.Rowguid).HasDefaultValueSql("(newid())");
        
        builder.Property(x => x.ModifiedDate).HasDefaultValueSql("(getdate())");
    }
}

Code DBContext

    public DbSet<BusinessEntity> BusinessEntities { get; set; }
    public DbSet<Person> Persons { get; set; }
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Student> Students { get; set; }
   

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(SchoolContext).Assembly);
    }

Add data

var businessEntity1 = new BusinessEntity() { };    
var studient = new Student() { BusinessEntity = businessEntity1, FirstName = "John", LastName = "Doe" , BirthDate = DateTime.Today };
    
dbContext.AddRange(businessEntity1, studient);   
dbContext.SaveChanges();
    
var id = dbContext.Persons.Select(i => i.BusinessEntityId).Single(e => e == businessEntity1.BusinessEntityId);

dbContext.Database.ExecuteSqlRaw($"EXEC ( 'INSERT INTO Employees (BusinessEntityId) VALUES ({1});')", id ); //Another solution to add an employee who is already a student

Data reader

Console.WriteLine("Students");
foreach (var p in context.Students)
{
    Console.WriteLine(p.ToString());
}

using var context1 = new SchoolContext(option); // I can't use the same context
Console.WriteLine("Employees");
foreach (var p in context1.Employees)
{
    Console.WriteLine(p.ToString());
}

[ajcvickers]

@Diaz15h Inheritance does not seem the appropriate mechanism to model this in C#. A person object can either be an Employee instance or a Student instance, but not both, and it cannot change from one to the other.

[Diaz15h]

thank you for your reply.
Currently this code allows me to manage a person as much as a student and an employee.
But I wonder if we can reset the DbContext instance to avoid creating a new one.

[ajcvickers]

@Diaz15h I would recommend against trying to mutate types. It's easy to get into inconsistent states. Instead, it would be better to not map this database schema to an inheritance hierarchy, but instead use composition.

[Diaz15h]

Thanks for the advice, it's only been a few months since I started learning programming.