Is there a better way to handle relational table in Entity Framework Core?

Question

That is a simplified example, but I have a Student class with a Relation enum that describes the relationship between students. I store this data as a Dictionary<Student, Relation>:

public class Student
{
    public int Id { get; set; }

    public string? Name { get; set; }

    public Dictionary<Student, Relation> Relations { get; set; } = new();
}

public enum Relation
{
    None = 0,
    Friend = 1,
    Relative = 2
}

My goal is to create a database with students and students_relations tables, that would look something like this:

students:

id	name
1	Tom
2	Alice
3	Bob

students_relations:

student_id	related_student_id	relation
1	3	1
3	2	2
2	3	2

While Tom may think about Bob as a Friend, Bob may have a different opinion, so there's no entry like 3/1/1 in the table. But Relative relations are different, so there are two entries for Bob and Alice.

I'm studying the usage of Entity Framework Core to complete this task. I'm well aware, that dictionary can't be used as property with EF Core, so what is the best approach to complete the task?

I've created a StudentRelations class for this:

[PrimaryKey("StudentId", "RelatedStudentId")]
public class StudentRelations
{
    [ForeignKey("StudentId")]
    public Student Student { get; set; } = null!;

    [ForeignKey("RelatedStudentId")]
    public Student RelatedStudent { get; set; } = null!;
    
    public Relation Relation { get; set; }
}

And here's my ApplicationContext class:

public class ApplicationContext : DbContext
{
    public DbSet<Student> Students { get; set; } = null!;

    public DbSet<StudentRelations> StudentRelations { get; set; } = null!;

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<StudentRelations>()
                    .HasOne(sr => sr.Student)
                    .WithMany(s => s.Relations);

        modelBuilder.Entity<StudentRelations>()
                    .HasOne(sr => sr.RelatedStudent);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=students.db")
                      .UseSnakeCaseNamingConvention();;
    }
}

This attempt also requires changing the Dictionary in Student class to List<StudentRelations> Relations.

It does what I need to, but still I'm wondering if I'm doing it the best way possible and if there's a way to map this table to Dictionary somehow. Or maybe my database scheme sucks and there's a better way to initialize database, so I won't need a Dictionary?

Seems fine to me... but I don't get why do you need a dictionary when you are having a List ? Is the Questsion about how to get a dictionary from the Relations or about the database design or both ? — jeb, May 10 '23 at 12:41
Sorry if my question wasn't clear enough. I'd prefer to use only Dictionary without List property and especially additional StudentRelations class. And yes, I'm not sure the database design is good in this case, so I wanted to clear this out too. I just thought, that if Dictionary isn't mapped automatically on EF Core, then it might be something wrong with my vision of db design. — Gigas002, May 10 '23 at 13:01
It seems you want a Many-to-many and join table with payload. Please see [this article in the microsoft documentation](https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many#many-to-many-and-join-table-with-payload). EF core doesn't have automatic mapping to and from a dictionary. You would have to set your own convertor up. — JHBonarius, May 10 '23 at 13:05
Thank you, I'll try this out. It seems very similar to what I was looking for — Gigas002, May 10 '23 at 13:26

Is there a better way to handle relational table in Entity Framework Core?

0 Answers0