SQLKata return complex object with Include/IncludeMany

Question

I need to return from a query a list of students where each students has assignments.

class Student {
  public string Name{ get; set; }
  public IEnumerable<Assignment> Assignments{ get; set; }
}

class Assignment { 
  public string Title { get; set; } 
}

so tried to create two queries and then use include/includemany but i get other errors (The given key 'Id' was not present in the dictionary)

var students = db.Query('Student')
    .Select(...)
    .Where()

var assignments= db.Query('Assignment')
    .Select(...)
    .Where()

var result = students.IncludeMany('Assignments', assignments).Get()

but this does not work. How can you properly create complex/nested objects with SQLKata?

Answer 1

I asume your example is complete, but you should add the primairy and foreign key properties on the queries. (They are not in your classes so I added them based on conventions sql kata uses)

var students = db.Query("Student")
    .Select("Id", "Name");

var assignments = db.Query("Assignment")
    .Select("StudentId", "Title");

var result = students
    .IncludeMany("Assignments", assignments)
    .Get();

If they are not specified as an argument to the IncludeMany method, SqlKata will use the "Id" field as primary key and the "StudentId" field as the foreign key in the relationship. You can add these as extra arguments to the IncludeMany method.

var result = students
    .IncludeMany("Assignments", assignments, "StudentId", "Id")
    .Get();

Please note that at this moment SqlKata doesn't seem to support this on the generic method Get<> GetAsync<>.