EF - Exercise 1

• Create a new Dotnet Console Application.
• Create a folder Entities and add the following classes.

public class Student
{
    public Student() {}

    public Student(string name)
    {
        Name = name;
    }

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

    public List<CourseEnrollment> CourseEnrollments { get; set; }
}

public class Course
{
    public Course() {}

    public Course(string courseName)
    {
        CourseName = courseName;
    }

    public int Id { get; set; }
    public string CourseName { get; set; }
}

public class CourseEnrollment
{
    public CourseEnrollment() {}
    public CourseEnrollment(Student student, Course course, string enrollmentPeriod)
    {
        Student = student;
        Course = course;
        EnrollmentPeriod = enrollmentPeriod;
    }

    public int StudentId { get; set; }
    public Student Student { get; set; }

    public int CourseId { get; set; }
    public Course Course { get; set; }

    public int? Grade { get; set; }
    public string EnrollmentPeriod { get; set; }
}

• Create a database context named SchoolContext
• Define a composite primary key on the m:n table by overriding this method in your context:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<CourseEnrollment>()
        .HasKey(o => new { o.StudentId, o.CourseId, o.EnrollmentPeriod });
}

• Make sure you have installed all required NuGet packages to connect to your database.
• Create a database migration and apply it to the database.
• Use this Program.cs file and implement the missing functionality.

public static class Program
{
    public static async Task Main(string[] args)
    {
        await SetupTestData();

        await UpdateGrades();

        await Query1();

        await Query2();

        await Query3();

        await Query4();

        await CeanupOldData();
    }

    private static async Task SetupTestData()
    {
        Console.Write("Setting up test data ... ");
        await using var context = new SchoolContext();

        if (await context.Students.AnyAsync())
        {
            Console.WriteLine("SKIPPED");
            return;
        }

        var students = new List<Student>()
        {
            new Student("Fritz"),
            new Student("Ellie"),
            new Student("Ferdl"),
            new Student("Lisa")
        };

        var courses = new List<Course>()
        {
            new Course("Software Entwicklung 4"),
            new Course("Datenbanken 4"),
            new Course("Verteilte Systeme 5"),
            new Course("Programming in COBOL 1")
        };

        var enrollments = new List<CourseEnrollment>()
        {
            new CourseEnrollment(students[0], courses[0], "2023/24"),
            new CourseEnrollment(students[1], courses[0], "2023/24"),
            new CourseEnrollment(students[2], courses[0], "2023/24"),
            new CourseEnrollment(students[3], courses[0], "2023/24"),
            new CourseEnrollment(students[0], courses[1], "2023/24"),
            new CourseEnrollment(students[1], courses[1], "2023/24"),
            new CourseEnrollment(students[2], courses[1], "2023/24"),
            new CourseEnrollment(students[3], courses[1], "2023/24"),
            new CourseEnrollment(students[1], courses[2], "2023/24"),
            new CourseEnrollment(students[0], courses[3], "1992/93")
        };

        await context.Students.AddRangeAsync(students);
        await context.Courses.AddRangeAsync(courses);
        await context.CourseEnrollments.AddRangeAsync(enrollments);
        await context.SaveChangesAsync();

        Console.WriteLine("DONE");
    }

    private static async Task UpdateGrades()
    {
        Console.Write("Setting grades ... ");
        // TODO: update existing CourseEnrollments, setting some grades.
        // TODO: give at least one 1, one 5, and leave at least one grade NULL


        Console.WriteLine("DONE");
    }

    private static async Task Query1()
    {
        Console.WriteLine("List all students that have at least one 5 as a grade ...");
        // TODO

        Console.WriteLine("DONE");
    }

    private static async Task Query2()
    {
        Console.WriteLine("List the average grade for each student ...");
        // TODO

        Console.WriteLine("DONE");
    }

    private static async Task Query3()
    {
        Console.WriteLine("List the minimum and maximum grade for each course ...");
        // TODO

        Console.WriteLine("DONE");
    }

    private static async Task Query4()
    {
        Console.WriteLine("List all students where a grade has not been given yet");
        // TODO: output should be like:
        // student 1
        //   course with missing grade 1
        //   course with missing grade 2
        //   ...
        // student 2
        //   course with missing grade 1
        //   ...


        Console.WriteLine("DONE");
    }

    private static async Task CeanupOldData()
    {
        Console.WriteLine("Delete COBOL Programming course and every course enrollment linked to it.");
        // TODO

        Console.WriteLine("DONE");
    }
}