I am having an issue trying to create a one to one relationship to a pivot table. These are my models
public class StudentCourse
{
public int Id { get; set; }
public int StudentId { get; set; }
public virtual Student Student { get; set; }
public int CourseId { get; set; }
public virtual Course Course { get; set; }
}
public class Student
{
public int Id { get; set; }
public string Name { get; set; }
public virtual IList<StudentCourse> StudentCourses { get; set; }
}
public class Course
{
public int Id { get; set; }
public string CourseName { get; set; }
public string Description { get; set; }
public virtual IList<StudentCourse> StudentCourses { get; set; }
}
public class ExamResult
{
public int Id { get; set; }
public int Marks { get; set; }
public int TotalMarks { get; set; }
public virtual StudentCourse StudentCourse { get; set; }
}
This is the model builder config where I have defined my many to many relationship between Student and Course
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<StudentCourse>().HasKey(sc => new { sc.StudentId, sc.CourseId });
modelBuilder.Entity<StudentCourse>().HasAlternateKey(e => e.Id);
modelBuilder.Entity<StudentCourse>().Property(e => e.Id).ValueGeneratedOnAdd();
modelBuilder.Entity<StudentCourse>()
.HasOne(sc => sc.Student)
.WithMany(s => s.StudentCourses)
.HasForeignKey(sc => sc.StudentId)
.OnDelete(DeleteBehavior.Cascade);
modelBuilder.Entity<StudentCourse>()
.HasOne(sc => sc.Course)
.WithMany(s => s.StudentCourses)
.HasForeignKey(sc => sc.CourseId)
.OnDelete(DeleteBehavior.Cascade);
}
And this is the error message I am getting when I run the migrations
Failed executing DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [StudentCourses] (
[StudentId] int NOT NULL,
[CourseId] int NOT NULL,
[Id] int NOT NULL IDENTITY,
CONSTRAINT [PK_StudentCourses] PRIMARY KEY ([StudentId], [CourseId]),
CONSTRAINT [AK_StudentCourses_Id] UNIQUE ([Id]),
CONSTRAINT [FK_StudentCourses_Courses_CourseId] FOREIGN KEY ([CourseId]) REFERENCES [Courses] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_StudentCourses_ExamResults_Id] FOREIGN KEY ([Id]) REFERENCES [ExamResults] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_StudentCourses_Students_StudentId] FOREIGN KEY ([StudentId]) REFERENCES [Students] ([Id]) ON DELETE CASCADE
);
Cascading foreign key 'FK_StudentCourses_ExamResults_Id' cannot be created where the referencing column 'StudentCourses.Id' is an identity column.
Could not create constraint or index. See previous errors.
Essentially I am trying to point to a StudentCourse record from the ExamResult table, but for some reason it's failing. Does anyone have any idea?