private void SeedDatabase(UniversityContext _ctx) { _ctx.Database.EnsureCreated(); var courses = new List <Course> { new Course { Name = "English", TeacherName = "William Shakespeare", Location = "Building 3 Room 301" }, new Course { Name = "Psychology", TeacherName = "Sigmund Freud", Location = "Building 4 Room 401" }, new Course { Name = "Philosophy", TeacherName = "Baruch Spinoza", Location = "Building 5 Room 501" } }; var students = new List <Student> { new Student { Name = "Thomas Stearns Eliot", Age = 18 }, new Student { Name = "Jacques Marie Émile Lacan", Age = 21 }, new Student { Name = "Friedrich Nietzsche", Age = 22 }, new Student { Name = "Quentin Tarantino", Age = 18 }, new Student { Name = "Lars von Trier", Age = 21 }, new Student { Name = "Christopher Nolan", Age = 22 } }; var courseStudents = new List <CourseStudent> { new CourseStudent { Course = courses[0], Student = students[0] }, new CourseStudent { Course = courses[1], Student = students[1] }, new CourseStudent { Course = courses[2], Student = students[2] }, new CourseStudent { Course = courses[2], Student = students[0] }, new CourseStudent { Course = courses[1], Student = students[0] }, new CourseStudent { Course = courses[1], Student = students[2] } }; _ctx.AddRange(courses); _ctx.AddRange(students); _ctx.AddRange(courseStudents); _ctx.SaveChanges(); }
static void Main(string[] args) { using (var dbContext = new UniversityContext()) { var s1 = new Student { FName = "Cristian", LName = "Anghelenici" }; var s2 = new Student { FName = "Eliodor", LName = "Tralala" }; var s3 = new Student { FName = "Ramil", LName = "Abdullaev" }; var a1 = new Address { strada = "Studenilor", ZipCode = "retg", Student = s1 }; var a2 = new Address { strada = "Moscova 2/1", ZipCode = "dsfg", Student = s2 }; var c1 = new Course { Name = "OOP" }; var c2 = new Course { Name = "AAC" }; var c3 = new Course { Name = "SQL" }; var sc11 = new StudentCourse { Course = c1, Student = s1, Mark = 9 }; var sc12 = new StudentCourse { Course = c1, Student = s2, Mark = 8 }; var sc13 = new StudentCourse { Course = c1, Student = s3, Mark = 10 }; var sc21 = new StudentCourse { Course = c2, Student = s1, Mark = 6 }; var sc22 = new StudentCourse { Course = c2, Student = s2, Mark = 9 }; var sc23 = new StudentCourse { Course = c2, Student = s3, Mark = 8 }; dbContext.AddRange(new List <StudentCourse> { sc11, sc12, sc13, sc21, sc22, sc23 }); dbContext.AddRange(new List <Address> { a1, a2 }); dbContext.AddRange(new List <Course> { c3 }); //dbContext.SaveChanges(); } using (var dbContext = new UniversityContext()) { //1. Da toti studentii si adresele lor dar daca nu au adrese dai una orsicare; IEnumerable <Student> students1 = dbContext.Students.Include(x => x.Address); var students2 = students1.Select(x => new { Name = x.FName + " " + x.LName, Address = x.Address ?? new Address { strada = "re", ZipCode = "dsf", StudentId = x.Id } }); foreach (var s in students2) { Console.WriteLine($"{s.Name}, {s.Address.strada}, {s.Address.ZipCode}"); } //2. select all course name witch have students that live on "Studentilor" var s1 = dbContext.StudentCourses.Where(x => x.Student.Address.strada == "Studenilor").Select(x => x.Course.Name).ToList(); var s11 = dbContext.StudentCourses .Include(x => x.Course) .Include(x => x.Student) .ThenInclude(x => x.Address) .Where(x => x.Student.Address.strada == "Studenilor") .Select(x => x.Course.Name).ToList(); //3. show all address of student that attend course "oop" var s32 = dbContext.StudentCourses.Where(x => x.Course.Name == "OOP").Select(x => x.Student.Address); //show student and avg of mark var f38 = dbContext.Students .Select(x => new { student = x, AvgMark = x.StudentCourses.Average(y => y.Mark) }).ToList(); var f39 = dbContext.Students.GroupBy(x => x.LName).Select(x => new { Student = x.Key, AvgMark = x.Average(p => p.StudentCourses.Average(d => d.Mark)) }); //4. show student and avg of mark where mark > avg(Mark) var f40 = dbContext.Students.GroupBy(x => x.LName).Select(x => new { Student = x.Key, AvgMark = x.Average(p => p.StudentCourses.Average(d => d.Mark)) }).Where(x => x.AvgMark > dbContext.StudentCourses.Average(p => p.Mark)); //5. show all address of students that attend more than 2 courses var s22 = dbContext.Students .Where(x => x.StudentCourses.Count() > 2).Select(x => x.Address).ToList(); //6. show haow many courses each student attend var v4 = dbContext.Students.Select(x => new { Name = x.FName, CountCourse = x.StudentCourses.Count() }); //7. show all student that attend "sql" course var v55 = dbContext.StudentCourses.Where(x => x.Course.Name == "OOP").Select(x => x.Student).ToList(); //show min mark for each student var v6 = dbContext.Students.Select(x => new { Name = x.FName, MinMark = x.StudentCourses.Min(p => p.Mark), MaxMark = x.StudentCourses.Max(p => p.Mark) }).ToList(); //8. show all students and list of marks var v7 = dbContext.Students.Select(x => new { Student = x, CoursesAndMark = x.StudentCourses.Select(y => new { Mark = y.Mark, Cours = y.Course.Name }) }); //9. select avg mark for all courses si chiar daca nari noti var f4r = dbContext.Courses.Select(x => new { Course = x, AvgMark = x.StudentCourses.Count() > 0 ? x.StudentCourses.Average(p => p.Mark) : 0 }).ToList(); //10. selecteaza toate numele la student , curs , nota var tto = dbContext.StudentCourses.Select(x => new { NameStudent = x.Student.FName, Curs = x.Course.Name, Mark = x.Mark }).Where(x => x.Mark > dbContext.StudentCourses.Average(p => p.Mark)).ToList(); //11. toate adresele la fiecare student var hhh = dbContext.Students.Select(x => new { Student = x, Adresa = x.Address.strada }); //12. grup join dintre studentcurses si student var groupJoin = dbContext.StudentCourses.GroupJoin(dbContext.Students, x => x.StudentId, x => x.Id, (sc, s) => new { hz = s.Select(m => m.LName), Nume = sc.Mark }); //13. selecteaza toti studentii cu notele lui var groupJoin2 = dbContext.Students.GroupJoin(dbContext.StudentCourses, x => x.Id, x => x.StudentId, (s, sc) => new { Name = s.FName, Nume = sc.Select(p => p.Mark) }).ToList(); var groupJoin2Suquery = dbContext.Students.Select(x => new { Name = x.FName, Marks = x.StudentCourses.Select(p => p.Mark) }); //14. toti studentii care nu au adresa var bnn = dbContext.Students.Where(x => x.Address == null); //15. toti studentii care nu au adresa si dai adresa var bnn2 = dbContext.Students.Where(x => x.Address == null).Select(x => new { Studen = x, Address = new Address { strada = "cei mai buna ", ZipCode = "fartovii" } }); //16. afisiaza toti studentii media la note ordonate descresc dupa nota si (si cu cit e mai mica decit nota precedenta) var studentDto = dbContext.Students.Select(x => new StudentDto { Name = x.FName, AvgMark = x.StudentCourses.Average(p => p.Mark), }).OrderByDescending(x => x.AvgMark).ToList(); for (int i = 1; i < studentDto.Count; i++) { studentDto[i].Diferenta = studentDto[i].AvgMark - studentDto[i - 1].AvgMark; } //17. notele la studenc la care numele se incepe cu "C" var students = dbContext.Students.Where(x => x.FName.StartsWith("C")).Select(x => new { Student = x, Mark = x.StudentCourses.Select(p => p.Mark) }); foreach (var s in studentDto) { Console.WriteLine($"Name: {s.Name}, AvgMark: {s.AvgMark}, Diferenta: {s.Diferenta}"); } //toate datele pentru pentru id =1 } Console.ReadKey(); }