Replacing a property from an Entity for another property that lives on another table

Question

I know the question sucks, but it is exactly the reason I can not find an answer.

So I have an Entity ("TeacherEntity") that represents a Table of the Database. One of the Properties represents a Foreign Key that maps to another Table ("StudentEntity"). In the ("Teacher") table the students appear as numbers.

public class TeacherEntity()
{
    public int TeacherId;
    public string Name;
}

public class StudentEntity()
{
    public int StudentId;
    public string StudenName;
    public int TeacherId;
}

I also have a ViewModel Class ("StudentViewModel").

public class StudentViewModel()
{
   public int Id;
   public string Name;
   public string TeacherName;
}

What I am trying to achieve is when I read out a list of Students with my Repository.

public List<TblShisha> GetStudents()
{
    return _context.StudentEntity.OrderBy(o => o.Name).ToList();
}

I should mention that I am using AutoMapper in my Startup folder in case that makes a difference.

 cfg.CreateMap<Entities.StudentEntity, Models.StudentViewModel>()
     .ForMember(dest => dest.TeacherName, opt => opt.MapFrom(src => $" 
     {src.TeacherEntity.Name}"));

I want to have the name of the Teacher for this Student instead of their Id. Do I have to read out the List of Students and replace the Teacher Id´s with their names on the controller or is there a better way?

Answer 1

It can be done by setting up a DbContext with the two tables laid out.

Context

public class TeacherStudentContext : DbContext
{
    public virtual DbSet<Student> Student { get; set; }
    public virtual DbSet<Teacher> Teacher { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(@"<connString>");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Student>(entity =>
        {
            entity.Property(e => e.Name).IsRequired();

            entity.HasOne(d => d.Teacher)
                .WithMany(p => p.Student)
                .HasForeignKey(d => d.TeacherId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_Student_Teacher");
        });

        modelBuilder.Entity<Teacher>(entity =>
        {
            entity.Property(e => e.Name).IsRequired();
        });
    }
}

Teacher

public class Teacher
{
    public Teacher()
    {
        Student = new HashSet<Student>();
    }

    public int TeacherId { get; set; }
    public string Name { get; set; }

    public ICollection<Student> Student { get; set; }
}

Student

public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
    public int TeacherId { get; set; }

    public Teacher Teacher { get; set; }
}

Now you can simply use LINQ to navigate at any point without having to include the Teacher table.

var vm = _context.Student.Select(x => new StudentViewModel{
    Id = x.StudentId,
    Name = x.Name,
    TeacherName = x.Teacher.Name
});

Or you can use Automapper obviously

Answer 2

I would add a property of type Teacher to the Student. Then in the query you can do:

_context.StudentEntity.Include("Teacher").OrderBy(o => o.Name).ToList();

Then you can populate your viewmodel with Student.Teacher.Name