CODEWITHSUNDEEP
c#entity-frameworkef-code-first
Z Daneshi
Z Daneshi

Reputation: 57

How to create relations from 2 different tables to same column in 3rd table using EF code first?

I have 2 completely different tables with thousand rows of data:

public class Student
{
    public Guid StudentUserId { get; set; }
    // Fields for students
}

public class Teacher
{
    public Guid TeacherUserId { get; set; }
    // Fields for teachers
}

public class Player
{
    public long Id { get; set; }

    // Extra Fields
    public Guid PlayerUserId { get; set; }

    public Teacher Teacher { get; set; }

    public Student Student { get; set; }

    // Fields for Player no difference between teacher and student, JUST PLAYER
}

In the Player class, there's a field of type Guid that can store StudentGuid or TeacherGuid.

So I have to connect both StudentUserId and TeacherUserId to PlayerUserId.

So I created a map like this:

public class PlayerMap : EntityTypeConfiguration<Player>
{
    public PlayerMap()
    {
        HasKey(x => x.Id);

        //...

        HasRequired(x => x.Teacher)
            .WithMany()
            .HasForeignKey(x => x.PlayerUserId);
        HasRequired(x => x.Student)
            .WithMany()
            .HasForeignKey(x => x.PlayerUserId);
    }
}

But I get this error:

the Dependent Role refers to the key properties, the upper bound of the multiplicity of the Dependent Role must be '1'.

if this relation is not correct, How can I handle this situation. I can't delete my data and I have to create 3rd table somehow keep previous data.

Upvotes: 0

Views: 41

Answers (2)

Vivek Nuna
Vivek Nuna

Reputation: 1

You cannot have one column as foreign key which is referring to two tables.

Though @Mark answer is answering your question. But you can have a different approach.

As you said that all the fields are same in both tables, so you can have only a single table, which will have a type field, it will tell you type whether its Student or Teacher. You can make Id and Type as composite primary key and refer in the other tables. So you can achieve whatever you want only using one column.

Upvotes: 0

marc_s
marc_s

Reputation: 754398

You cannot have a single foreign key column that refers to one table some times, and to another table at other times.

If you have a situation like this, you need to have two separate foreign key fields - one for student, one for teacher - and make sure at most one of them is filled with a value at any given time.

public class Player
{
    public long Id { get; set; }

    // Foreign Key for "Teacher"
    public Guid? TeacherId { get; set; }

    // Foreign Key for "Student"
    public Guid? StudentId { get; set; }

    public Teacher Teacher { get; set; }
    public Student Student { get; set; }

    // Fields for Player no difference between teacher and student, JUST PLAYER
}

public class PlayerMap : EntityTypeConfiguration<Player>
{
    public PlayerMap()
    {
        HasKey(x => x.Id);
        //...

        HasOptional(x => x.Teacher)
            .WithMany()
            .HasForeignKey(x => x.TeacherId);
        HasOptional(x => x.Student)
            .WithMany()
            .HasForeignKey(x => x.StudentId);
    }
}

Upvotes: 1

Related Questions