static void Main()
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion<StudentSystemContext, Configuration>());
StudentSystemContext studentSystemDb = new StudentSystemContext();
foreach (var item in studentSystemDb.Students)
{
Console.WriteLine("Name: {0} with studentNumber {1}",item.StudentName +' '+ item.StudentLastName,item.StudentNumber);
}
studentSystemDb.SaveChanges();
}
static void Main()
{
var db = new StudentSystemContext();
//03. Seed the database with random values -> the Seed method of Configuration class
var thirdStudent = db.Students.Where(student => student.StudentID == 3).First();
var homeworksOfThirdStudent = db.Homeworks.Where(homework => homework.StudentID == thirdStudent.StudentID).ToList();
Console.WriteLine(thirdStudent.Name + "has written his homework for: ");
foreach (var homework in homeworksOfThirdStudent)
{
Console.WriteLine("\"" + homework.Course.Name + "\"");
}
}
public static void Main(string[] args)
{
var context = new StudentSystemContext();
var material = new Material
{
Content = "Material"
};
var course = new Course
{
Description = "We learn databases",
Name = "DB"
};
course.Materials.Add(material);
var homework = new Homework
{
Content = "Task",
Course = course,
Datesent = DateTime.Now
};
var student = new Student
{
Name = "Pesho",
StudentNumber = "12345"
};
student.Courses.Add(course);
student.Homeworks.Add(homework);
context.Students.Add(student);
context.SaveChanges();
}
private static void Seed(StudentSystemContext db)
{
}
private static void SeedStudents(StudentSystemContext db, int count)
{
StudentGenetaror.InitialStudentSeed(db, count);
}
private static void SeedHomeworks(StudentSystemContext db, int count)
{
HomeworkGenerator.InitialHomeworkSeed(db, count);
}
private static void SeedStudentsCourses(StudentSystemContext db, int count)
{
StudentsCoursesGenerator.InitialStudentCoursesSeed(db, count);
}
private static void Seed(StudentSystemContext context)
{
Course cSharpCourse = new Course
{
Description = "hardcore programming",
Name = "C#",
StartDate = DateTime.ParseExact("20-11-2017", "dd-MM-yyyy", null),
EndDate = DateTime.ParseExact("20-12-2018", "dd-MM-yyyy", null),
Price = 500.00m
};
context.Courses.Add(cSharpCourse);
Course javaCourse = new Course
{
Description = "Oracle programming",
Name = "Java",
StartDate = DateTime.ParseExact("10-11-2017", "dd-MM-yyyy", null),
EndDate = DateTime.ParseExact("10-12-2018", "dd-MM-yyyy", null),
Price = 650.00m
};
context.Courses.Add(javaCourse);
Student cSharpStudent = new Student
{
Name = "Ivan",
PhoneNumber = "*88",
RegisteredOn = DateTime.ParseExact("10-01-2017", "dd-MM-yyyy", null)
};
context.Students.Add(cSharpStudent);
Student javaStudent = new Student
{
Name = "Georgi",
PhoneNumber = "123",
RegisteredOn = DateTime.ParseExact("18-05-2017", "dd-MM-yyyy", null)
};
context.Students.Add(javaStudent);
Resource cSharpResource = new Resource
{
Course = cSharpCourse,
Name = "C# Tasks",
ResourceType = ResourceType.Document,
Url = $"www.courses./{cSharpCourse.Name}"
};
context.Resources.Add(cSharpResource);
Resource javaResource = new Resource
{
Course = cSharpCourse,
Name = "Java tasks",
ResourceType = ResourceType.Document,
Url = $"www.courses./{javaCourse.Name}"
};
context.Resources.Add(javaResource);
Homework cSharpHomework = new Homework
{
Student = cSharpStudent,
Course = cSharpCourse,
Content = "C# problems",
ContentType = ContentType.Pdf,
SubmissionTime = DateTime.ParseExact("20-12-2017", "dd-MM-yyyy", null)
};
context.HomeworkSubmissions.Add(cSharpHomework);
Homework javaHomework = new Homework()
{
Student = javaStudent,
Course = javaCourse,
Content = "Java problems",
ContentType = ContentType.Pdf,
SubmissionTime = DateTime.ParseExact("20-01-2018", "dd-MM-yyyy", null)
};
context.HomeworkSubmissions.Add(javaHomework);
StudentCourse cSharpAssignment = new StudentCourse
{
Student = cSharpStudent,
Course = cSharpCourse
};
context.StudentCourses.Add(cSharpAssignment);
StudentCourse javaAssignment = new StudentCourse
{
Student = cSharpStudent,
Course = javaCourse
};
context.StudentCourses.Add(javaAssignment);
context.SaveChanges();
}
private static void Seed(StudentSystemContext db)
{
var students = new List <Student>
{
new Student {
Name = "Pesho"
},
new Student {
Name = "Merry"
},
new Student {
Name = "Ivan", PhoneNumber = "0889876543"
},
new Student {
Name = "Nikola"
},
new Student {
Name = "Katq", PhoneNumber = "0886854333"
}
};
db.Students.AddRange(students);
var courses = new List <Course>
{
new Course {
Name = "Basics", StartDate = DateTime.Now, EndDate = (DateTime.Parse("5/2/2018")), Price = 100.00m
},
new Course {
Name = "Fundamentals", Price = 260.00m
},
new Course {
Name = "Java", Price = 180.00m
},
new Course {
Name = "Db Entity Framework", Price = 269.00m
}
};
db.Courses.AddRange(courses);
var resources = new List <Resource>
{
new Resource {
Name = "Introduction", ResourceType = ResourceType.Document, Course = courses[0]
},
new Resource {
Name = "Basic Functionality", ResourceType = ResourceType.Presentation, Course = courses[2]
},
new Resource {
Name = "Exam Preparation", ResourceType = ResourceType.Document, Course = courses[1]
},
new Resource {
Name = "Loops", ResourceType = ResourceType.Video, Course = courses[1]
},
new Resource {
Name = "Entity Relation", ResourceType = ResourceType.Document, Course = courses[3]
},
new Resource {
Name = "Entity Relation", ResourceType = ResourceType.Video, Course = courses[3]
},
new Resource {
Name = "Install Visual Studio", ResourceType = ResourceType.Other, Course = courses[0]
},
new Resource {
Name = "Introduction", ResourceType = ResourceType.Presentation, Course = courses[1]
},
new Resource {
Name = "Exercise", ResourceType = ResourceType.Document, Course = courses[2]
},
new Resource {
Name = "Condition Statements", ResourceType = ResourceType.Video, Course = courses[0]
}
};
db.Resources.AddRange(resources);
var hwsubmissions = new List <Homework>
{
new Homework {
ContentType = ContentType.Pdf, Course = courses[0], Student = students[0]
},
new Homework {
ContentType = ContentType.Application, Course = courses[2], Student = students[1]
},
new Homework {
ContentType = ContentType.Zip, Course = courses[3], Student = students[4]
},
new Homework {
ContentType = ContentType.Zip, Course = courses[1], Student = students[2]
},
new Homework {
ContentType = ContentType.Application, Course = courses[3], Student = students[1]
},
new Homework {
ContentType = ContentType.Pdf, Course = courses[0], Student = students[1]
},
new Homework {
ContentType = ContentType.Pdf, Course = courses[0], Student = students[3]
},
new Homework {
ContentType = ContentType.Zip, Course = courses[1], Student = students[0]
},
new Homework {
ContentType = ContentType.Application, Course = courses[3], Student = students[4]
}
};
db.HomeworkSubmissions.AddRange(hwsubmissions);
var studentCourses = new List <StudentCourse>
{
new StudentCourse {
Student = students[0], Course = courses[0]
},
new StudentCourse {
Student = students[0], Course = courses[1]
},
new StudentCourse {
Student = students[1], Course = courses[0]
},
new StudentCourse {
Student = students[2], Course = courses[1]
},
new StudentCourse {
Student = students[3], Course = courses[0]
},
new StudentCourse {
Student = students[4], Course = courses[3]
},
new StudentCourse {
Student = students[4], Course = courses[1]
},
new StudentCourse {
Student = students[2], Course = courses[3]
},
new StudentCourse {
Student = students[1], Course = courses[2]
},
};
db.StudentCourses.AddRange(studentCourses);
db.SaveChanges();
}
public static void Main()
{
var context = new StudentSystemContext();
var studentsAndHomeworks = context.Students.Select(s =>
new
{
Name = s.StudentName,
Homeworks = s.Homeworks.Select(h =>
new
{
Content = h.HomeworkContent,
Type = h.HomeworkContentType
})
});
foreach (var student in studentsAndHomeworks)
{
Console.WriteLine("{0}: ", student.Name);
Console.WriteLine(string.Join(Environment.NewLine, student.Homeworks));
}
Console.WriteLine();
Console.WriteLine();
var coursesAndResources = context.Courses.OrderBy(c => c.StartDate).
ThenByDescending(c => c.EndDate).
Select(c =>
new
{
Name = c.CourseName,
Description = c.Description,
Resources = c.Resources
});
foreach (var course in coursesAndResources)
{
Console.WriteLine("{0}, {1} ", course.Name, course.Description);
foreach (var resource in course.Resources)
{
Console.WriteLine("{0}, {1}, {2}", resource.ResourceName,
resource.ResourceType, resource.URL);
}
}
Console.WriteLine();
Console.WriteLine();
var coursesWithMoreThan5Resources = context.Courses.
Where(c => c.Resources.Count >= 2).
OrderByDescending(c => c.Resources.Count).
ThenByDescending(c => c.StartDate).
Select(c =>
new
{
Name = c.CourseName,
ResourceCount = c.Resources.Count
});
foreach (var course in coursesWithMoreThan5Resources)
{
Console.WriteLine("{0}, Resource count: {1} ", course.Name, course.ResourceCount);
}
Console.WriteLine();
Console.WriteLine();
var studentInfo = context.Students.
Select(s =>
new {
Name = s.StudentName,
CoursesCount = s.Courses.Count,
CoursesSum = s.Courses.Sum(c => c.Price),
AverageCoursePrice = s.Courses.Average(c => c.Price)
});
foreach (var info in studentInfo)
{
Console.WriteLine("Name: {0}, Courses count: {1}, Courses sum: {2}, Avg price: {3} ",
info.Name, info.CoursesCount, info.CoursesSum, info.AverageCoursePrice);
}
}
private static void CreateNewDatabase()
{
var db = new StudentSystemContext();
db.Database.Delete();
db.Database.Create();
}
static void Main(string[] args)
{
using (var context = new StudentSystemContext())
{
}
}
public MarkController()
{
var context = new StudentSystemContext();
this.markRepository = new MarkRepository(context);
}
protected override void Seed(StudentSystemContext context)
{
//if (context.Students.Any() || context.Courses.Any() || context.Resources.Any() || context.Homeworks.Any())
//{
// return;
//}
var random = new Random();
using (var sr = new StreamReader(@"E:\SoftUni\Databases\Advanced\Relations\Exercises\StudentSystem\students.txt"))
{
sr.ReadLine();
var line = sr.ReadLine();
while (line != null)
{
var studentParams = line.Split(new[] { ' ' }, 5);
var studentName = studentParams[0] + " " + studentParams[1];
var phoneNumber = studentParams[2];
var birthDay = DateTime.ParseExact(studentParams[3], "dd/MM/yyyy", CultureInfo.InvariantCulture);
var registrationDate = DateTime.ParseExact(studentParams[4], "dd/MM/yyyy", CultureInfo.InvariantCulture);
var student = new Student {
Name = studentName, PhoneNumber = phoneNumber, BirthDay = birthDay, RegistrationDate = registrationDate
};
context.Students.AddOrUpdate(s => s.Name, student);
line = sr.ReadLine();
}
}
context.SaveChanges();
using (var sr = new StreamReader(@"E:\SoftUni\Databases\Advanced\Relations\Exercises\StudentSystem\courses.txt"))
{
sr.ReadLine();
var line = sr.ReadLine();
var students = context.Students.Local;
while (line != null)
{
var data = line.Split(new[] { ';' }, 5);
var name = data[0];
var description = data[1];
var startDate = DateTime.ParseExact(data[2], "dd/MM/yyyy", CultureInfo.InvariantCulture);
var endDate = DateTime.ParseExact(data[3], "dd/MM/yyyy", CultureInfo.InvariantCulture);
var price = decimal.Parse(data[4]);
//var studentsCount = random.Next(0, students.Count());
//var courseStudents = new HashSet<Student>();
//for (var i = 1; i <= studentsCount; i++)
//{
// var studentIndex = random.Next(0, studentsCount);
// var student = students[studentIndex];
// courseStudents.Add(student);
//}
//var course = new Course()
//{
// Name = name,
// Description = description,
// StartDate = startDate,
// EndDate = endDate,
// Price = price,
// Students = courseStudents
//};
var course = context.Courses.FirstOrDefault(c => c.Name == name) ?? new Course
{
Name = name,
Description = description,
StartDate = startDate,
EndDate = endDate,
Price = price,
};
var studentsCount = random.Next(0, students.Count + 1);
var courseStudents = course.Students;
for (var i = 1; i <= studentsCount; i++)
{
var studentIndex = random.Next(0, studentsCount);
var student = students[studentIndex];
courseStudents.Add(student);
}
context.Courses.AddOrUpdate(c => c.Name, course);
line = sr.ReadLine();
}
}
context.SaveChanges();
using (var sr = new StreamReader(@"E:\SoftUni\Databases\Advanced\Relations\Exercises\StudentSystem\resources.txt"))
{
sr.ReadLine();
var line = sr.ReadLine();
var courses = context.Courses.Local;
var coursesCount = courses.Count;
while (line != null)
{
var data = line.Split(new[] { ';' }, 4);
var name = data[0];
var resourceType = (ResourceType)Enum.Parse(typeof(ResourceType), data[1]);
var url = data[2];
var courseIndex = random.Next(0, coursesCount);
var course = courses[courseIndex];
//var resource = context.Resources.FirstOrDefault(r => r.Name == name);
//if (resource != null)
//{
// resource.Course = course;
//}
//else
//{
// resource = new Resource()
// {
// Name = name,
// ResourceType = resourceType,
// Url = url,
// Course = course
// };
//}
var resource = new Resource
{
Name = name,
ResourceType = resourceType,
Url = url,
Course = course
};
context.Resources.AddOrUpdate(r => r.Name, resource);
line = sr.ReadLine();
}
}
context.SaveChanges();
using (var sr = new StreamReader(@"E:\SoftUni\Databases\Advanced\Relations\Exercises\StudentSystem\homeworks.txt"))
{
sr.ReadLine();
var line = sr.ReadLine();
var courses = context.Courses.Local;
var coursesCount = courses.Count;
//var students = context.Students;
//var studentsCount = students.Count();
while (line != null)
{
var data = line.Split(new[] { ';' }, 5);
var content = data[0];
var contentType = (ContentType)Enum.Parse(typeof(ContentType), data[1]);
var submissionDate = DateTime.ParseExact(data[2], "dd/MM/yyyy", CultureInfo.InvariantCulture);
var courseIndex = random.Next(0, coursesCount);
var course = courses[courseIndex];
var students = course.Students.ToArray();
var studentsCount = students.Count();
var studentIndex = random.Next(0, studentsCount);
var student = students[studentIndex];
//var homework = context.Homeworks.FirstOrDefault(h => h.Content == content);
//if (homework != null)
//{
// homework.Course = course;
// homework.Student = student;
//}
//else
//{
// homework = new Homework
// {
// Content = content,
// ContentType = contentType,
// SubmissionDate = submissionDate,
// Student = student,
// Course = course
// };
//}
var homework = new Homework
{
Content = content,
ContentType = contentType,
SubmissionDate = submissionDate,
Student = student,
Course = course
};
//student.Homeworks.Add(homework);
//course.Homeworks.Add(homework);
context.Homeworks.AddOrUpdate(h => h.Content, homework);
line = sr.ReadLine();
}
}
context.SaveChanges();
using (var reader = new StreamReader(@"E:\SoftUni\Databases\Advanced\Relations\Exercises\StudentSystem\licenses.txt"))
{
reader.ReadLine();
var name = reader.ReadLine();
while (name != null)
{
name = name.Trim();
var resources = context.Resources.Local;
var resourcesCount = resources.Count;
var resourceIndex = random.Next(0, resourcesCount);
var resource = resources[resourceIndex];
var license = context.Licenses.FirstOrDefault(l => l.Name == name);
if (license != null)
{
license.Resource = resource;
}
else
{
license = new License {
Name = name, Resource = resource
};
}
context.Licenses.AddOrUpdate(l => l.Name, license);
name = reader.ReadLine();
}
}
context.SaveChanges();
}
private void InitialSeed(StudentSystemContext context)
{
if (context.Students.Any() || context.Cources.Any() || context.Homeworks.Any() || context.Resources.Any())
{
return;
}
// seed courses
Course sql = new Course
{
CourseName = "SQL",
Description = "procedures, triggers, transactions, views",
StartDate = DateTime.Now.AddMonths(-1),
EndDate = DateTime.Now.AddMonths(3),
Price = 100m
};
Course csharp = new Course
{
CourseName = "C#",
Description = "interfaces, delegates, events",
StartDate = DateTime.Now.AddMonths(-1),
EndDate = DateTime.Now.AddMonths(3),
Price = 200m
};
Course js = new Course
{
CourseName = "JS",
Description = "objects, prototypes, delegation",
StartDate = DateTime.Now.AddMonths(-1),
EndDate = DateTime.Now.AddMonths(3),
Price = 300m
};
context.Cources.AddOrUpdate(sql, csharp, js);
// seed students
Student aPeters = new Student
{
Name = "Andrew Peters",
RegisteredOn = DateTime.Now,
Birthday = DateTime.Now.AddYears(-26),
Courses = new[] { sql, csharp }
};
Student bLambson = new Student
{
Name = "Brice Lambson",
RegisteredOn = DateTime.Now,
Birthday = DateTime.Now.AddYears(-34),
Courses = new[] { csharp }
};
Student rMiller = new Student
{
Name = "Rowan Miller",
RegisteredOn = DateTime.Now,
Birthday = DateTime.Now.AddYears(-18),
Courses = new[] { csharp, js }
};
context.Students.AddOrUpdate(aPeters, bLambson, rMiller);
//seed Homework
Homework aPetersHomeworkCS = new Homework
{
Content = "c# homework",
ContentType = ContentType.application,
SubmissionDate = DateTime.Now,
Student = aPeters,
};
Homework aPetersHomeworkSQL = new Homework
{
Content = "sql homework",
ContentType = ContentType.zip,
SubmissionDate = DateTime.Now,
Student = aPeters
};
Homework bLambsonHomeworkCS = new Homework
{
Content = "c# homework",
ContentType = ContentType.application,
SubmissionDate = DateTime.Now,
Student = bLambson
};
Homework rMillerHomeworkCS = new Homework
{
Content = "c# homework",
ContentType = ContentType.application,
SubmissionDate = DateTime.Now,
Student = rMiller
};
Homework rMillerHomeworkJS = new Homework
{
Content = "JS homework",
ContentType = ContentType.application,
SubmissionDate = DateTime.Now,
Student = rMiller
};
context.Homeworks.AddOrUpdate(aPetersHomeworkCS, aPetersHomeworkSQL, bLambsonHomeworkCS, rMillerHomeworkCS, rMillerHomeworkJS);
//seed resouces
Resource csharpPresentation = new Resource()
{
Name = "c# Presentation",
ResourceType = ResourceType.presentation,
Url = "www.uni.com/csharp",
Course = csharp
};
Resource csharpLecture = new Resource()
{
Name = "c# Lecture",
ResourceType = ResourceType.video,
Url = "www.uni.com/csharp",
Course = csharp
};
Resource sqlPresentation = new Resource()
{
Name = "sql Presentation",
ResourceType = ResourceType.presentation,
Url = "www.uni.com/sql",
Course = sql
};
Resource sqlLecture = new Resource()
{
Name = "sql Lecture",
ResourceType = ResourceType.video,
Url = "www.uni.com/sql",
Course = sql
};
Resource jsPresentation = new Resource()
{
Name = "js Presentation",
ResourceType = ResourceType.presentation,
Url = "www.uni.com/js",
Course = js
};
Resource jsLecture = new Resource()
{
Name = "js Lecture",
ResourceType = ResourceType.video,
Url = "www.uni.com/js",
Course = js
};
context.Resources.AddOrUpdate(csharpPresentation, csharpLecture, sqlPresentation, sqlLecture, jsPresentation, jsLecture);
}
static void Main()
{
Database.SetInitializer(new MigrateDatabaseToLatestVersion <StudentSystemContext, Configuration>());
StudentSystemContext context = new StudentSystemContext();
// 01. All students with their homework
var studentsWithHomewrk = context.Students
.Select(s => new { s.Name, Homework = s.Homeworks.Select(h => new { h.Content, h.ContentType }).ToList() })
.ToList();
//foreach (var student in studentsWithHomewrk)
//{
// Console.WriteLine(student.Name);
// foreach (var homework in student.Homework)
// {
// Console.WriteLine($"Type: {homework.ContentType}, Content: {homework.Content}");
// }
//}
// 02. All courses and their resources
var coursesAndResources =
context.Courses.OrderBy(c => c.StartDate)
.ThenByDescending(c => c.EndDate)
.Select(c => new { c.Name, c.Description, c.Resources });
//foreach (var courses in coursesAndResources)
//{
// Console.WriteLine($"Course name: {courses.Name}, Course description: {courses.Description}");
// foreach (var resource in courses.Resources)
// {
// Console.WriteLine(resource);
// }
//}
// 03. All courses with more than 5 resources (haven't seeded such but oh well)
context.Courses
.Where(c => c.Resources.Count > 5)
.OrderByDescending(c => c.Resources.Count)
.ThenByDescending(c => c.StartDate)
.Select(c => new { c.Name, c.StartDate })
.ToList();
//.ForEach(x => Console.WriteLine($"{x.Name} {x.StartDate}"));
// 04. Active courses on a given date and students in 'em
DateTime dateTime = DateTime.Parse("2016-08-01");
context.Courses.Where(c => c.StartDate <dateTime && c.EndDate> dateTime)
.OrderByDescending(c => c.Students.Count)
.ThenByDescending(c => SqlFunctions.DateDiff("day", c.StartDate, c.EndDate))
.Select(
c => new { c.Name, c.StartDate, c.EndDate, Duration = SqlFunctions.DateDiff("day", c.StartDate, c.EndDate), c.Students.Count })
.ToList();
//.ForEach(
// x =>
// Console.WriteLine(
// $"Course: {x.Name} Period: {x.StartDate} - {x.EndDate} ({x.Duration} days). Attented by {x.Count} students "));
// 05. All courses for students and some aggregates
context.Students
.Where(s => s.Courses.Any())
.OrderByDescending(s => s.Courses.Sum(c => c.Price))
.ThenByDescending(s => s.Courses.Count)
.ThenBy(s => s.Name)
.Select(
s =>
new
{
s.Name,
s.Courses.Count,
AvgPrice = s.Courses.Select(c => c.Price).Average(),
Total = s.Courses.Sum(c => c.Price)
})
.ToList()
.ForEach(x => Console.WriteLine($"{x.Name} {x.Count} {x.AvgPrice} {x.Total}"));
// 08. TagAttribute
}
static void Main()
{
var context = new StudentSystemContext();
var studentsConut = context.Students.Count();
// Problem 3
/* 3.1. Lists all students and their homework submissions. Select only their names and for each homework - content and
* content-type */
var stHom = context.Homeworks.Where(h => h.StudentId == 1);
foreach (var h in stHom)
{
Console.WriteLine(h.Content);
}
/*
* var studentsHomeworks =
* context.Students.Select(s => new
* {
* s.Name,
* Homeworks = s.Courses.Select(c => c.Homeworks.Select(h => new
* {
* h.Content,
* h.ContentType
* }))
* });
*
* foreach (var sh in studentsHomeworks)
* {
* Console.WriteLine("{0}, Homeworks: ", sh.Name);
* Console.WriteLine(new string('-', 25));
*
* foreach (var course in sh.Homeworks)
* {
* foreach (var h in course)
* {
* Console.WriteLine("Content: {0}; Content Type: {1}", h.Content, h.ContentType);
* }
* }
* Console.WriteLine();
* }
*/
/* 3.2. List all courses with their corresponding resources. Select the course name and description and everything for
* each resource. Order the courses by start date (ascending), then by end date (descending) */
/*
* var courses = context.Courses
* .OrderBy(c=> c.StartDate)
* .ThenByDescending(c => c.EndDate)
* .Select(c => new
* {
* c.Name,
* c.Description,
* Resources = c.Resources.Select(r => new
* {
* r.Name,
* r.ResourceType,
* r.URL
* })
* });
*
* foreach (var c in courses)
* {
* Console.WriteLine("Course: {0}; Description: {1}", c.Name, c.Description);
* Console.WriteLine("Resources:");
* foreach (var r in c.Resources)
* {
* Console.WriteLine("{0}, Type: {1}", r.Name, r.ResourceType);
* Console.WriteLine("Link: {0}", r.URL);
* }
* Console.WriteLine(new string('-', 25));
* Console.WriteLine();
* }
*/
/* 3.3. List all courses with more than 5 resources. Order them by resources count (descending),
* then by start date (descending). Select only the course name and the resource count */
/*
* var bigCourses = context.Courses
* .Where(c => c.Resources.Count > 5)
* .OrderByDescending(c => c.Resources.Count)
* .ThenByDescending(c => c.StartDate)
* .Select(c => new
* {
* c.Name,
* ResourcesCount = c.Resources.Count
* });
*
* foreach (var c in bigCourses)
* {
* Console.WriteLine("Course: {0}, ResorsesCount = {1}", c.Name, c.ResourcesCount);
* }
*/
/* 3.4. List all courses which were active on a given date (choose the date depending on the data seeded to ensure there
* are results), and for each course count the number of students enrolled. Select the course name, start and end date,
* course duration (difference between end and start date) and number of students enrolled.
* Order the results by the number of students enrolled (in descending order), then by duration (descending). */
/*
* var activeCourses = context.Courses
* .Where(c => c.StartDate < DateTime.Now && c.EndDate > DateTime.Now)
* .ToList()
* .OrderByDescending(c => c.Students.Count)
* .ThenByDescending(c => (c.EndDate - c.StartDate).TotalDays)
* .Select(c => new
* {
* c.Name,
* c.StartDate,
* c.EndDate,
* Duration = (c.EndDate - c.StartDate).TotalDays,
* StudentsCount = c.Students.Count
* });
*
* foreach (var c in activeCourses)
* {
* Console.WriteLine("Active Course: {0}, Start: {1}, End: {2}", c.Name, c.StartDate, c.EndDate);
* Console.WriteLine("Duration: {0} days, Students Count: {1}", c.Duration, c.StudentsCount);
* Console.WriteLine();
* }
*/
/* 3.5. For each student, calculate the number of courses she’s enrolled in, the total price of these courses and the
* average price per course for the student. Select the student name, number of courses, total price and average price.
* Order the results by total price (descending), then by number of courses (descending) and
* then by the student’s name (ascending). */
/*
* var allStudents = context.Students
* .OrderByDescending(s => s.Courses.Sum(c => c.Price))
* .ThenByDescending(s => s.Courses.Count)
* .ThenBy(s => s.Name)
* .Select(s => new
* {
* s.Name,
* CoursesCount = s.Courses.Count,
* TotalPrice = s.Courses.Sum(c => c.Price),
* AvgPrice = s.Courses.Sum(c => c.Price) / s.Courses.Count
* //AveragePrice = s.Courses.Average(c => c.Price)
* });
*
* foreach (var s in allStudents)
* {
* Console.WriteLine("Student: {0}, Courses enrolled in: {1}", s.Name, s.CoursesCount);
* Console.WriteLine("Total price: {0:f2} lv., Average price: {1:f2} lv.", s.TotalPrice, s.AvgPrice);
* Console.WriteLine();
* }
*/
var start = DateTime.Parse("01.12.2015");
var end = DateTime.Parse("30-Jan-2015");
// Console.WriteLine(start);
Console.WriteLine(start);
}
private void SeedDbWithSampleData(StudentSystemContext db)
{
var studentOne = new Student
{
Name = "Gosho",
RegistrationDate = new DateTime(2000, 10, 19),
Birthday = new DateTime(1989, 01, 01),
PhoneNumber = "0887999123"
};
var studentTwo = new Student
{
Name = "Pesho",
RegistrationDate = new DateTime(2001, 11, 20),
Birthday = new DateTime(1989, 01, 01),
PhoneNumber = "0887999123"
};
var studentThree = new Student
{
Name = "Ivan",
RegistrationDate = new DateTime(2002, 12, 21),
Birthday = new DateTime(1989, 01, 01),
PhoneNumber = "0887999123"
};
var courseOne = new Course
{
Name = "C#",
StartDate = new DateTime(2015, 01, 02),
EndDate = new DateTime(2015, 02, 03),
Price = 210M,
Description = "A course about C#"
};
var courseTwo = new Course
{
Name = "Java",
StartDate = new DateTime(2015, 05, 02),
EndDate = new DateTime(2015, 06, 10),
Price = 200M,
Description = "Java stuff"
};
var courseThree = new Course
{
Name = "JS",
StartDate = new DateTime(2015, 08, 02),
EndDate = new DateTime(2015, 09, 12),
Price = 150M,
Description = "JavaScript things"
};
db.Students.Add(studentOne);
db.Students.Add(studentTwo);
db.Students.Add(studentThree);
db.Courses.Add(courseOne);
db.Courses.Add(courseTwo);
db.Courses.Add(courseThree);
db.SaveChanges();
studentOne = db.Students
.Include(s => s.Courses)
.Include(s => s.Homework)
.FirstOrDefault(s => s.Name == "Gosho");
studentTwo = db.Students
.Include(s => s.Courses)
.Include(s => s.Homework)
.FirstOrDefault(s => s.Name == "Pesho");
studentThree = db.Students
.Include(s => s.Courses)
.Include(s => s.Homework)
.FirstOrDefault(s => s.Name == "Ivan");
courseOne = db.Courses
.Include(c => c.Students)
.Include(c => c.Resources)
.Include(c => c.HWSubmissions)
.FirstOrDefault(c => c.Name == "C#");
courseTwo = db.Courses
.Include(c => c.Students)
.Include(c => c.Resources)
.Include(c => c.HWSubmissions)
.FirstOrDefault(c => c.Name == "Java");
courseThree = db.Courses
.Include(c => c.Students)
.Include(c => c.Resources)
.Include(c => c.HWSubmissions)
.FirstOrDefault(c => c.Name == "JS");
studentOne.Courses.Add(new StudentCourse
{
CourseId = courseOne.Id
});
studentOne.Courses.Add(new StudentCourse
{
CourseId = courseThree.Id
});
studentTwo.Courses.Add(new StudentCourse
{
CourseId = courseTwo.Id
});
studentThree.Courses.Add(new StudentCourse
{
CourseId = courseOne.Id
});
studentThree.Courses.Add(new StudentCourse
{
CourseId = courseTwo.Id
});
studentThree.Courses.Add(new StudentCourse
{
CourseId = courseThree.Id
});
courseOne.Resources.Add(new Resource
{
Name = "SampleTextOne",
ResourceType = Models.Enums.ResourceType.Document,
Url = "www.softuni.bg"
});
courseOne.Resources.Add(new Resource
{
Name = "SampleTextTwo",
ResourceType = Models.Enums.ResourceType.Document,
Url = "www.softuni.bg"
});
courseOne.Resources.Add(new Resource
{
Name = "SampleTextThree",
ResourceType = Models.Enums.ResourceType.Document,
Url = "www.softuni.bg"
});
courseOne.Resources.Add(new Resource
{
Name = "SampleTextFour",
ResourceType = Models.Enums.ResourceType.Document,
Url = "www.softuni.bg"
});
courseOne.Resources.Add(new Resource
{
Name = "SampleTextFive",
ResourceType = Models.Enums.ResourceType.Document,
Url = "www.softuni.bg"
});
courseOne.Resources.Add(new Resource
{
Name = "SampleTextSix",
ResourceType = Models.Enums.ResourceType.Document,
Url = "www.softuni.bg"
});
courseTwo.Resources.Add(new Resource
{
Name = "SampleVideo",
ResourceType = Models.Enums.ResourceType.Video,
Url = "www.youtube.com"
});
courseThree.Resources.Add(new Resource
{
Name = "SimplePresentation",
ResourceType = Models.Enums.ResourceType.Presentation,
Url = "www.google.com"
});
studentOne.Homework.Add(new Homework
{
Content = "BlablaST1",
ContentType = Models.Enums.ContentType.Pdf,
SubmissionDate = new DateTime(2017, 10, 20),
CourseId = courseOne.Id
});
studentOne.Homework.Add(new Homework
{
Content = "BlablaST12",
ContentType = Models.Enums.ContentType.Zip,
SubmissionDate = new DateTime(2017, 10, 25),
CourseId = courseTwo.Id
});
studentTwo.Homework.Add(new Homework
{
Content = "BlablaST2",
ContentType = Models.Enums.ContentType.Pdf,
SubmissionDate = new DateTime(2017, 10, 2),
CourseId = courseOne.Id
});
studentThree.Homework.Add(new Homework
{
Content = "BlablaST3",
ContentType = Models.Enums.ContentType.Application,
SubmissionDate = new DateTime(2017, 10, 10),
CourseId = courseThree.Id
});
db.SaveChanges();
var licenseOne = new License
{
Name = "SimpleResourceOne",
ResourceId = 1
};
var licenseTwo = new License
{
Name = "SimpleResourceTwo",
ResourceId = 1
};
var licenseThree = new License
{
Name = "SimpleResourceThree",
ResourceId = 2
};
db.Licenses.Add(licenseOne);
db.Licenses.Add(licenseTwo);
db.Licenses.Add(licenseThree);
db.SaveChanges();
}
private static void ResetDatabase(StudentSystemContext db)
{
db.Database.EnsureDeleted();
db.Database.Migrate();
Seed(db);
}
public BaseController()
{
this.context = new StudentSystemContext();
}
public SchoolsController()
{
StudentSystemContext context = new StudentSystemContext();
this.data = new EfRepository <School>(context);
}
static void Main()
{
StudentSystemContext dbContext = new StudentSystemContext();
dbContext.Database.EnsureCreated();
}
private static void Main()
{
var db = new StudentSystemContext();
db.Database.EnsureCreated();
}
static void Main()
{
var context = new StudentSystemContext();
// 1. Lists all students and their homework submissions.
// Select only their names and for each homework - content and content-type.
var studentsWithHomeworks = context.Students
.Select(s => new
{
s.Name,
Homeworks = s.Homeworks
.Select(h => new
{
h.Content,
h.ContentType
})
.ToList()
})
.ToList();
foreach (var student in studentsWithHomeworks)
{
Console.WriteLine("-- {0}", student.Name);
if (student.Homeworks.Any())
{
foreach (var homework in student.Homeworks)
{
Console.WriteLine("[{0}] - {1}", homework.Content, homework.ContentType);
}
}
else
{
Console.WriteLine("(No homeworks)");
}
}
// 2. List all courses with their corresponding resources.
// Select the course name and description and everything for each resource.
// Order the courses by start date (ascending), then by end date (descending).
var coursesWithResources = context.Courses
.OrderBy(c => c.StartDate)
.ThenByDescending(c => c.EndDate)
.Select(c => new
{
c.Name,
c.Description,
Resources = c.Resources.ToList()
})
.ToList();
foreach (var course in coursesWithResources)
{
Console.WriteLine("-- {0} - {1}", course.Name, course.Description);
foreach (var resource in course.Resources)
{
Console.WriteLine("{0} - {1} - {2}", resource.Name, resource.ResourceType, resource.URL);
}
}
// 3. List all courses with more than 5 resources.
// Order them by resources count (descending), then by start date (descending).
// Select only the course name and the resource count.
var coursesWithMoreThan5Resources = context.Courses
.OrderByDescending(c => c.Resources.Count)
.ThenByDescending(c => c.StartDate)
.Select(c => new
{
c.Name,
c.Resources.Count
})
.ToList();
foreach (var course in coursesWithMoreThan5Resources)
{
Console.WriteLine("-- {0} - {1}", course.Name, course.Count);
}
// 4. List all courses which were active on a given date (choose the date depending
// on the data seeded to ensure there are results), and for each course count the number of students enrolled.
// Select the course name, start and end date, course duration (difference between end and start date)
// and number of students enrolled. Order the results by the number of students
// enrolled (in descending order), then by duration (descending).
DateTime specificDate = new DateTime(2014, 7, 5);
var activeCoursesOnGivenDate = context.Courses
.Where(c => c.StartDate <= specificDate && specificDate <= c.EndDate)
.OrderByDescending(c => c.Students.Count())
.ThenByDescending(c => EntityFunctions.DiffDays(c.StartDate, c.EndDate))
.Select(c => new
{
c.Name,
c.StartDate,
c.EndDate,
Duration = EntityFunctions.DiffDays(c.StartDate, c.EndDate),
StudentsCount = c.Students.Count
})
.ToList();
foreach (var course in activeCoursesOnGivenDate)
{
Console.WriteLine("-- {0}, start: {1}, end: {2}, duration: {3} days, students: {4}",
course.Name,
course.StartDate,
course.EndDate,
course.Duration,
course.StudentsCount);
}
// 5. For each student, calculate the number of courses he/she has enrolled in,
// the total price of these courses and the average price per course for the student.
// Select the student name, number of courses, total price and average price.
// Order the results by total price (descending), then by number of courses (descending)
// and then by the student's name (ascending).
var studentsWithCourses = context.Students
.Where(s => s.Courses.Any())
.OrderByDescending(s => s.Courses.Sum(c => c.Price))
.ThenByDescending(s => s.Courses.Count)
.ThenBy(s => s.Name)
.Select(s => new
{
s.Name,
EnrolledCourses = s.Courses.Count,
TotalPrice = s.Courses.Sum(c => c.Price),
AveragePrice = s.Courses.Average(c => (int)c.Price)
})
.ToList();
foreach (var student in studentsWithCourses)
{
Console.WriteLine("-- {0}, courses: {1}, total price: {2}, average price: {3:f2}",
student.Name,
student.EnrolledCourses,
student.TotalPrice,
student.AveragePrice);
}
}
private static void SeedResources(StudentSystemContext db)
{
ResouceGenerator.InitialResourseSeed(db);
}
// Problem 01 - Code First Student System
// Problem 02 - Seed Some Data in the Database
// Problem 03 - Working with the Database
// Problem 04 - Resource Licenses
public static void Main()
{
StudentSystemContext context = new StudentSystemContext();
context.Database.Initialize(true);
}
private static void SeedCourses(StudentSystemContext db, int count)
{
CourseGenerator.InitialCourseSeed(db, count);
}
private static void SeedStudents(StudentSystemContext context)
{
StudentGenerator.InitialStudentsSeed(context);
}
static void Seed(StudentSystemContext dbContext)
{
var students = new[]
{
new Student
{
Birthday = new DateTime(1985, 2, 3),
Name = "Pesho Ivanov",
PhoneNumber = "0888123456",
RegisteredOn = new DateTime(2015, 6, 7)
},
new Student
{
Name = "Gosho Petrov",
RegisteredOn = new DateTime(2016, 3, 5)
},
new Student
{
Name = "Stamat Ivanov",
Birthday = new DateTime(1995, 1, 7),
RegisteredOn = new DateTime(2016, 12, 20)
},
new Student
{
Name = "Mihail Stamatov",
RegisteredOn = new DateTime(2017, 1, 7),
PhoneNumber = "0877445566"
}
};
dbContext.Students.AddRange(students);
var courses = new[]
{
new Course
{
Name = "JS Fundamentals",
Description = "JS for beginners",
StartDate = new DateTime(2016, 9, 18),
EndDate = new DateTime(2016, 10, 20),
Price = 180.00m
},
new Course
{
Name = "JS Advanced",
StartDate = new DateTime(2016, 10, 21),
EndDate = new DateTime(2016, 11, 19),
Price = 180.00m
},
new Course
{
Name = "Js Applications",
StartDate = new DateTime(2016, 11, 20),
EndDate = new DateTime(2016, 12, 18),
Description = "JS Приложения",
Price = 200.00m
}
};
dbContext.Courses.AddRange(courses);
var resources = new[]
{
new Resource
{
Name = "Intro",
Url = "softuni.bg/resources/0123456789",
ResourceType = ResourceType.Presentation,
Course = courses[0]
},
new Resource
{
Name = "OOP Intro",
Url = "softuni.bg/resources/1245687",
ResourceType = ResourceType.Video,
Course = courses[1]
},
new Resource
{
Name = "Objects",
Url = "softuni.bg/resources/556688",
ResourceType = ResourceType.Document,
Course = courses[0]
}
};
dbContext.Resources.AddRange(resources);
var homeworks = new[]
{
new Homework
{
Content = "softuni.bg/homeworks/124578",
ContentType = ContentType.Zip,
SubmissionTime = new DateTime(2016, 2, 5, 12, 45, 55),
Course = courses[0],
Student = students[2]
},
new Homework
{
Content = "softuni.bg/homeworks/225588",
ContentType = ContentType.Pdf,
SubmissionTime = new DateTime(2017, 5, 8, 14, 22, 36),
Course = courses[1],
Student = students[0]
},
new Homework
{
Content = "softuni.bg/homeworks/44778855",
ContentType = ContentType.Application,
SubmissionTime = new DateTime(2017, 4, 6, 18, 22, 54),
Course = courses[1],
Student = students[2]
}
};
dbContext.HomeworkSubmissions.AddRange(homeworks);
var studentcourses = new[]
{
new StudentCourse
{
Student = students[0],
Course = courses[0]
},
new StudentCourse
{
Student = students[1],
Course = courses[0]
},
new StudentCourse
{
Student = students[1],
Course = courses[2]
}
};
dbContext.StudentCourses.AddRange(studentcourses);
dbContext.SaveChanges();
}
private static void SeedCourses(StudentSystemContext context)
{
CourseGenerator.InitialCourseSeed(context);
}
public static void Main()
{
using (var dbContext = new StudentSystemContext())
{
}
}
private static void SeedResources(StudentSystemContext context)
{
ResourceGenerator.InitialResourceSeed(context);
}
private static void SeedHomeworkSubmissions(StudentSystemContext context)
{
HomeworkSubmissionsGenerator.InitialHomeworkSubmissionSeed(context);
}
private static void Seed(StudentSystemContext db)
{
var Students = new[]
{
new Student()
{
Name = "Atanas Kambitov",
RegisteredOn = Convert.ToDateTime("01-02-2000"),
PhoneNumber = "1234567890"
},
new Student()
{
Name = "Asen Kambitov",
RegisteredOn = Convert.ToDateTime("07-01-2010"),
PhoneNumber = "1131562890"
},
new Student()
{
Name = "Stefan Kambitov",
RegisteredOn = Convert.ToDateTime("05-05-2008"),
BirthDay = Convert.ToDateTime("05-02-1974"),
PhoneNumber = "1232543210"
},
new Student()
{
Name = "Anton Stoqnov",
RegisteredOn = Convert.ToDateTime("03-09-2009"),
BirthDay = Convert.ToDateTime("11-11-1974"),
PhoneNumber = "1131212840"
}
};
db.Students.AddRange(Students);
var Courses = new[]
{
new Course()
{
Name = "Programming Basics",
Description = "Easy course for beginners !",
StartDate = Convert.ToDateTime("25-05-2016"),
EndDate = Convert.ToDateTime("29-06-2016"),
Price = 330
},
new Course()
{
Name = "Programming Fundamentals",
StartDate = Convert.ToDateTime("25-07-2016"),
EndDate = Convert.ToDateTime("29-08-2016"),
Price = 390
},
new Course()
{
Name = "Software Technologies",
Description = "Learn deferent working technologies !",
StartDate = Convert.ToDateTime("05-09-2016"),
EndDate = Convert.ToDateTime("09-08-2016"),
Price = 350
},
new Course()
{
Name = "Databases Basics",
Description = "Basic learning of databases !",
StartDate = Convert.ToDateTime("25-05-2016"),
EndDate = Convert.ToDateTime("29-06-2016"),
Price = 290
},
};
db.Courses.AddRange(Courses);
var StudentCourses = new[]
{
new StudentCourse()
{
Student = Students[1], Course = Courses[1]
},
new StudentCourse()
{
Student = Students[2], Course = Courses[1]
},
new StudentCourse()
{
Student = Students[0], Course = Courses[0]
},
new StudentCourse()
{
Student = Students[0], Course = Courses[2]
}
};
db.StudentsCourses.AddRange(StudentCourses);
var Resourses = new[]
{
new Resource()
{
Name = "ResourseOne",
Url = "https://www.softuni.bg",
ResourceType = ResourceType.Document,
CourseId = Courses[2].CourseId
},
new Resource()
{
Name = "ResourseTwo",
Url = "https://www.youtube.bg",
ResourceType = ResourceType.Video,
CourseId = Courses[0].CourseId
},
new Resource()
{
Name = "ResourseThree",
Url = "https://www.facebook.bg",
ResourceType = ResourceType.Other,
CourseId = Courses[2].CourseId
},
};
db.Resources.AddRange(Resourses);
var HomeworksSubmissions = new[]
{
new Homework()
{
Content = "contentOne",
ContentType = ContentType.Application,
SubmissionTime = Convert.ToDateTime("04-06-2015"),
StudentId = Students[0].StudentId,
CourseId = Courses[1].CourseId
},
new Homework()
{
Content = "contentTwo",
ContentType = ContentType.Zip,
SubmissionTime = Convert.ToDateTime("01-08-2011"),
StudentId = Students[1].StudentId,
CourseId = Courses[2].CourseId
},
new Homework()
{
Content = "contentOne",
ContentType = ContentType.Pdf,
SubmissionTime = Convert.ToDateTime("08-06-2017"),
StudentId = Students[2].StudentId,
CourseId = Courses[0].CourseId
}
};
db.Homework.AddRange(HomeworksSubmissions);
db.SaveChanges();
}
