[SQL] How to Perform Bulk Insert via Navigation Property in Entity Framework Core with Type...

I have the following database structure where Student and Course are linked through a many-to-many relationship in StudentCourseLinks. I need to perform a bulk insert into StudentCourseLinks where I insert a new CourseId for all Student records that belong to a specific SchoolId.

Here are my simplified models:

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

// Navigation property to many-to-many relationship
public ICollection<Course> Courses { get; set; }
}

public class Course
{
public int Id { get; set; }
public ICollection<Student> Students { get; set; }
}

public class StudentCourseLink
{
public int StudentId { get; set; }
public Student Student { get; set; }
public int CourseId { get; set; }
public Course Course { get; set; }
}


What I want to achieve is the following SQL query in Entity Framework Core, but with type safety and bulk operations (i.e., avoiding multiple round trips to the database):

INSERT INTO StudentCourseLinks (StudentId, CourseId)
SELECT s.Id, 1
FROM Students s
WHERE s.SchoolId = 1;


I want to add a new CourseId (e.g., 1) for all students that belong to SchoolId = 1, but I'd like to achieve this through Entity Framework Core's type-safe API, including using the navigation properties between Student and Course. I would like the conditional insert to also be dynamic since my conditions could change.

Is there a way to perform this bulk insert using Entity Framework Core, maintaining type safety and avoiding raw SQL?

Any guidance or solutions for this would be greatly appreciated!

Continue reading...