0

I'm struggling with many to many relationships using linq where there is no foreignkey but where the intersection table has been created for me behind the scenes based on a collection of entities on a model.

Given the following classes

public class BaseEntity
{
    public int Id { get; set; }

    public DateTime DateUpdated { get; set; }
}

public class Student : BaseEntity
{
    public string Name { get; set; }

    [JsonIgnore]
    public ICollection<Course> Courses { get; set; }
}

public class Course : BaseEntity
{
    public string Name { get; set; }

    public ICollection<Student> Students { get; set; }
}

and the tables that were created

dbo.Courses
dbo.Students
dbo.CourseStudent // (but this is not in my context class as ef generated it for me when I ran my migration)

I'm using Blazor and in one of my components I get a student, from there I want to get the courses that student is assigned to but I'm having trouble with the query and have tried the below and many others

// What I'm trying to achieve
// select * from courses where students.id == id
// or
// select * from courses where students contains student.id

public async Task<List<Course>> GetByStudent(int id) // studentId
{
    return await _db.Courses.GroupBy(x => x.Students.Where(x => x.Id == id)).ToList();
    return await _db.Courses.Include(x => x.Students).Where(x => x.Id == id).ToListAsync();                              
}

Thanks for reading, I do hope it makes sense!

Can anyone can point me in the right direction of how I can get all courses for a single student by the student navigation property.

Or, ways to access a navigation property using linq without a foreign key.

SSK
  • 3
  • 2
  • *`[JsonIgnore]`* - avoid serializing your db entities – Caius Jard May 31 '22 at 21:23
  • Yeah sorry, quite new - Didn't think courses needed to be exposed on on api/student and that was how I got rid of it, I'll read in to avoiding that - Thanks for the pointer. – SSK May 31 '22 at 21:37
  • We tend to avoid it by having another set of objects used by the UI. In Blazor server it's less important because data doesn't really leave the server in the same way, but for other kinds of infrastructures it's quite typical to have a set of classes that are useful to the front end, and they look different to the db ents. They usually don't have cycles in their graphs so serializers don't go crazy ser'ing them. To easily map from the db entities to the view entities we might use a mapper like AutoMapper or Mapster – Caius Jard May 31 '22 at 21:43
  • Actually I am using dto classes for the student and course model, so I have a studentdto and coursedto that I am using with automapper - I have also used viewmodels before, which to me are the same kind of thing, is that right? Thank you – SSK May 31 '22 at 21:49

1 Answers1

1

direction of how I can get all courses for a single student by the student navigation property.

context.Students.Include(s => s.Courses).First(s => s.Id == studentId)

The Include makes EF write the joins to bring the related courses in for the student. That it's going through a mapping table is EF's job to sort out, rather than yours..

Side note, that though include does joining it isn't necessarily required in cases where you mention properties and navigations explicitly in a where or select clause. See Gert's detailed answer here for more info on the interplay between Include and Select

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • I actually think I just achieved it by doing _db.Courses.Where(x => x.Students.Any(x => x.Id == id)).ToListAsync(); Is there an advantage of one over the other? I suppose that may be in Gert's answer which I will check out, thanks! – SSK May 31 '22 at 21:46
  • *Is there an advantage of one over the other?* - one gets you the student details too? They might also map to different SQLs; the Include one will likely be a join whereas the Any will be a coordinated EXISTS, but execution would likely be mostly the same, perhaps the Any form uses a little less resource because it doesn't hit the students table – Caius Jard Jun 01 '22 at 14:03