What's the purpose of autogenerated linking table if you cannot use it from your C# Windows Application (using Linq)?

Admin 1
Viewed 41

The code below, is fine. It works. Things are inserted, stuff appear on the right side on the left side etc. However, there's an issue. Using a program such as LinQPad (https://www.linqpad.net), I am able to query the data and see it listed.

But, trying to do the same in C#, says that the "auto" generated linking table does not exists in the context.

Relationship: Many-to-Many.

I am using Entity Framework 6.

EDIT: What's the purpose of autogenerated linking table if you cannot use it from your C# Windows Application? Using Linq?

My Code First tables:

public class Student
{
    [Key, Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int StudentID { get; set; }
    public string StudentName { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public virtual ICollection<Course> Courses { get; set; }
}

public class Course
{
    [Key, Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int CourseId { get; set; }
    [Index("CourseName", 2, IsUnique = true)]
    public string CourseName { get; set; }

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

DBContext.cs

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
       .HasMany<Course>(s => s.Courses)
       .WithMany(c => c.Students)
       .Map(cs =>
       {
           cs.MapLeftKey("FK_StudentID");
           cs.MapRightKey("FK_CourseID");
           cs.ToTable("StudentCourse");
       });

    base.OnModelCreating(modelBuilder);
}

Querying from within the LinqPad, works perfectly fine:

void Main()
{
    var data = (from s in Students
    join sc in StudentCourse on s.StudentID equals sc.FK_StudentID
    join c in Courses on c.CourseId equals sc.FK_CourseID
    select new { s,c });
    
    Console.WriteLine(data);
}
entity-framework linq c#
1 Answers

There is nothing stopping you from declaring it as an entity, but where the linking table is kept to just the required keys, it is an optimization not to have to declare it when using the navigation properties.

The key issue I think you are facing here is you're writing Linq like SQL and completely missing the navigation properties.

For instance, if I want all Courses for a particular student: Without a linking table I can do:

var studentCourses = context.Students
    .Where(x => x.StudentId == studentId)
    .SelectMany(x => x.Courses)
    .ToList();

To get a list of each student/course combination as you outlined:

var studentCourses = context.Students
    .SelectMany(s => s.Courses.Select(c => new {s, c}))
    .ToList();

This gives me the list of courses for that student. I don't have to manually join up Student to Course through StudentCourse like writing an SQL statement.

Now you can certainly declare a StudentCourse entity, but this changes the relationships slightly. Rather than a Many-to-Many, you have a Many-to-One-to-Many:

public class Student
{
   // ...
   public virtual ICollection<StudentCourse> StudentCourses { get; set; } = new List<StudentCourse>();
}

public class Course
{
   // ...
   public virtual ICollection<StudentCourse> StudentCourses { get; set; } = new List<StudentCourse>();
}

public class StudentCourse
{
   [Key, Column(Order=0), ForeignKey(Student)]
   public int StudentId { get; set; }
   [Key, Column(Order=1), ForeignKey(Course)]
   public int CourseId { get; set; }
   public virtual Student Student { get; set; }
   public virtual Course Course { get; set; }
}

It can be tempting to name the StudentCourses property "Courses" and "Students" in their respective counterparts, but IMO this gets misleading when diving trough the navigation as I'll point out in the example below.

For those using EFCore, I believe this is still the only supported option for many-to-many. This is also a required option if you want to make any alterations to the joining table such as using a dedicated PK column, or other columns such as CreatedAt etc. to track edits etc. for the relationships.

Then to do the first example query in Linq you'd need to change it slightly to:

var studentCourses = context.Students
    .Where(x => x.StudentId == studentId)
    .SelectMany(x => x.StudentCourses.Course)
    .ToList();

To get a list of each student/course combination with this linking entity:

var studentCourses = context.StudentCourses.ToList();
// or, if there is other data in the linking entity and you just want student and course reference: 
var studentCourses = context.StudentCourses
  .Select(sc => new {sc.Student, sc.Course})
  .ToList();

Alternatively you could write the Linq QL statement like you did in Linqpad now that the entity is declared and can be added as a DbSet in the context. (DbSet is not required if you are using navigation properties)

Edit: added example below: (I can't vouch for it's correctness as I pretty much exclusively use the Fluent Linq syntax, not Linq QL)

var data = (from s in context.Students
join sc in context.StudentCourse on s.StudentID equals sc.StudentID
join c in context.Courses on c.CourseId equals sc.CourseID
select new { s,c });

This is why I recommend naming the property on Student as StudentCourses rather than Courses. The alternative would read:

    .SelectMany(x => x.Courses.Course)

Where Courses implies I should be getting courses (as the optimization to avoid the linking entity can give you) but you're getting StudentCourses so you're left with it looking rather weird as .Courses.Course to get the actual Course.

user_4234970
Related