Make access possible to dynamic table LINQ EF6 Code First

Question

The code after the tables here, does generate a new dynamic table in the Database. But, I don't know how to access it or how to add linking to it via C# code.

3NF, three tables.

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");
       });
}

The code above here, creates a table like this:

StudentCourse 
=================================
|  FK_StudentID  | FK_CourseID  |
=================================

EDIT: I am looking at this question now, How to define Many-to-Many relationship through Fluent API Entity Framework?

EDIT, Clarification: Sorry for my delay: This is more related to when, e.g. you already have students or add students separately. But, then want to dynamically, connect them to a course or a number of courses. Similarly, you add new courses. But, these can then be taken by students.

David Browne - Microsoft · Accepted Answer · 2021-01-06T17:52:57.727

But, I don't know how to access it or how to add linking to it via C# code.

In EF6 you don't access it directly. Instead just add/remove items to the Student.Courses or Course.Students navigation properties.

EG

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace Ef6Test
{

    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; } = new HashSet<Course>();
    }

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

        public virtual ICollection<Student> Students { get; } = new HashSet<Student>();
    }
    public class TestContext : DbContext
    {
        public DbSet<Student> Students { get; set; }
        public DbSet<Course> Courses { get; set; }
    }

    internal class Program
    {

        public static void Main(string[] args)
        {
            
            using (var db = new TestContext())
            {
                db.Database.Log = m => Console.WriteLine(m);

                if (db.Database.Exists())
                    db.Database.Delete();

                db.Database.Create();

                var s = new Student();
                db.Students.Add(s);
                s.Courses.Add(new Course());
                db.SaveChanges();
            }
        }

    }
}

outputs

Started transaction at 1/6/2021 11:52:17 AM -06:00

INSERT [dbo].[Courses]([CourseName])
VALUES (NULL)
SELECT [CourseId]
FROM [dbo].[Courses]
WHERE @@ROWCOUNT > 0 AND [CourseId] = scope_identity()


-- Executing at 1/6/2021 11:52:17 AM -06:00

-- Completed in 9 ms with result: SqlDataReader



INSERT [dbo].[Students]([StudentName], [DateOfBirth])
VALUES (NULL, NULL)
SELECT [StudentID]
FROM [dbo].[Students]
WHERE @@ROWCOUNT > 0 AND [StudentID] = scope_identity()


-- Executing at 1/6/2021 11:52:17 AM -06:00

-- Completed in 7 ms with result: SqlDataReader



INSERT [dbo].[StudentCourses]([Student_StudentID], [Course_CourseId])
VALUES (@0, @1)

-- @0: '1' (Type = Int32)

-- @1: '1' (Type = Int32)

-- Executing at 1/6/2021 11:52:17 AM -06:00

-- Completed in 17 ms with result: 1



Committed transaction at 1/6/2021 11:52:17 AM -06:00

Yes, but how do you tell it to build a relationship using their IDs as a new pair unique key? — , Jan 06 '21 at 16:56
FK_StudentID | FK_CourseID report NULL; even if I insert something in the other two tables. — , Jan 06 '21 at 17:08
David, what you are claiming here, doesn't make sense to me. Don't I need to explicitly specify this somewhere? — , Jan 06 '21 at 17:15
But, what if, you add Students first, and then later want these to connect. ? That's what I am doing ... I don't add both at the same time. — , Jan 06 '21 at 18:13
David, `var s = new Student(); db.Students.Add(s); s.Courses.Add(new Course()); db.SaveChanges();` Those lines tell me differently. — , Jan 06 '21 at 18:18
You're adding `new` Items. But, what if you added New students before. Closed the connection. Later opened a new connection, just to connect those students with the courses from another table. — , Jan 06 '21 at 18:19
In traditional MYSQL, I know how to do this. But, here I am doing it with the EF6 Code First and LINQ! Since this is a focused .NET environement. — , Jan 06 '21 at 18:20
For connecting existing entites, query them from the database and then use the Navigation properties. — David Browne - Microsoft, Jan 06 '21 at 18:36
David Browne, how come with this approach, I am getting: CourseID with 1, 2, whereas, StudentID 1, 1, inspite that there are two students and one course? Shouldn't it logically be: StudentID 1, 2 and CourseID 1, 1 if they both take the same course? **I am confused now** ! — , Jan 06 '21 at 18:53

Make access possible to dynamic table LINQ EF6 Code First

1 Answers1