How to handle many-to-many relationships using Spark?

Hi,

I am new to Spark so using the Student/Course example in the Spark documentation, where a Student can take any number of courses, and a course can by taken by any number of students, how do you go about setting up this many-to-many relationship? I am not using the Pro versions so I am hand-rolling my domain layer.

So for this trivial example I [possibly wrongly] assume I would have a domain.cs describing the entities like I do in attached code snippet.

But when I retrieve a student the collection of courses is empty, and likewise for a course. What do I need to do to include a list of associated courses for a student, and the associated students for a course?

Many thanks.

// domain.cs

public partial class Student : Entity<Student> {
    public Student() { Courses = new List<Course>(); }
    public Student(bool defaults) : base(defaults) { }

    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List Courses {get; set;}
}

public partial class Course : Entity<Course> {
    public Course() { Students= new List<Student>(); }
    public Course(bool defaults) : base(defaults) { }

    public int Id { get; set; }
    public string Name { get; set; }
    public List Students {get; set;}
}

public partial class StudentCourse : Entity<StudentCourse> {
    public int Id { get; set; }
    public int StudentId { get; set; }
    public int CourseId { get; set; }
}

// controller

// Get a list of students.  
var students = SchoolContext.Students.All().ToList();

foreach (var student in students)
{
    // Get a list of course ids for the student's id. 
    var studentCourses = SchoolContext.StudentCourses.All(where: "StudentId = @0", parms: student.Id);
    // Get a list of courses for the student
    student.Courses = SchoolContext.Courses.All(where: "Id IN (" + studentCourses.CommaSeparate(i => i.CourseId) + ")").ToList();
}

Mark Erasmus, Aug 28, 2015

Reply 1

Mark:

Below is some code I quickly put together. It has not been tested or validated.

A total of 3 SELECTs are executed on the database, as opposed to your example which could be many dozens of SELECT statements (depending on the number of students). As a matter of policy never place a database query in a loop, because this is always better handled on the database.

Once the students, courses, and student/courseIds are in memory you can very quickly resolve all courses for each student. Again, it is very important to always minimize the number of database hits.

As an aside, be sure you check that the comma separated studentIds and courseIds have a value or else your next query will fail.

A final note: in this scenario I would possibly consider writing the SQL manually with two JOINs and then execute just 1 SQL statement with the Context.Query method. From a database perspective it is the most efficient. On the coding side, the difference is that you get dynamic data element back, so there is no Intellisense.

Hope this helps.
Jack

var students = Context.Students.All();
var studentIds = students.CommaSeparate(s=>s.Id);

var studentCourses = Context.StudentCourses.All(where: "StudentId IN (" + studentIds + ")");
var courseIds = studentCourses.CommaSeparate(sc => sc.CourseId);

var courses = Context.Courses.All(where: "Id IN (" + courseIds + ")").ToDictionary(c => c.Id);

// resolve student -> course relationships entirely in memory (no more database access);

foreach(var student in students)
{
     var courseIds = studentCourses.Where( sc => sc.StudentId == student.Id );
     foreach(var courseId in courseIds)
     {
         student.Courses.Add(courses[courseId]);
     }
}

Jack Poorte, Sep 03, 2015

Reply 2

Hello Mark:

Your model is correct.

If you like to retrieve Students you just select Students.

If you like to retrieve Students with Courses then you have two ways to effectively handle this in SPARK.

1) Select Students, then collect their student ids and for those retrieve all Courses. These are 2 SELECT statements using the All method .

2) Write a SQL JOIN operation and retrieve all Students and their Courses in one operation using the Query method. Then iterate over the rows and populate your business objects manually, which should be easy.

Personally, I like method 1 better because I don't have to write SQL.

As an aside: Some ORMs will handle many-to-many automatically for you and they will retrieve Students with Courses. This may seem convenient, but it is anyone's guess how this is implemented. The ORM may not know how to optimize your particular queries. The above methods are super efficient and with SPARK you always know the SQL that executes on the database server.

Hope this helps.
Jack

Jack Poorte, Aug 31, 2015