private static void Main(string[] args)
{
var db = new StudentContext();
var courses = db.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 course in courses)
{
Console.WriteLine("- {0} has {1} resources", course.Name, course.ResourcesCount);
}
}
private static void Main()
{
var db = new StudentContext();
var courses = db.Courses
.Where(c => c.StartDate == new DateTime(2015, 2, 1))
.OrderByDescending(c => c.Students.Count)
.ThenByDescending(c => EntityFunctions.DiffDays(c.StartDate, c.EndDate))
.Select(c => new
{
c.Name,
StudentsCount = c.Students.Count,
Duration = EntityFunctions.DiffDays(c.StartDate,c.EndDate)
});
foreach (var course in courses)
{
Console.WriteLine("- {0} has {1} students and its duration is {2} days",course.Name, course.StudentsCount,course.Duration);
}
}
private static void Main()
{
var db = new StudentContext();
var students = db.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,
AvaragePrice=s.Courses.Average(c=>c.Price),
TotalPrice=s.Courses.Sum(c=>c.Price)
});
foreach (var student in students)
{
Console.WriteLine("{0} is in {1} the money he spend on those courses is {2} and the total value is {3}", student.Name, student.AvaragePrice, student.TotalPrice);
}
}
private static void Main(string[] args)
{
var db = new StudentContext();
var courses = db.Courses
.OrderBy(c=>c.StartDate).ThenByDescending(c=>c.EndDate)
.Select(c => new
{
c.Name,
c.Description,
c.Resources
});
foreach (var course in courses)
{
Console.WriteLine("-- {0} - {1}",course.Name,course.Description);
foreach (var resource in course.Resources)
{
Console.WriteLine("{0} {1} - {2} ",resource.Name,resource.Type,resource.Id);
}
}
}
static void Main(string[] args)
{
var db = new StudentContext();
var students = db.Students
.Select(s => new
{
s.Name,
Homework=s.Homeworks.Select(h=>new
{
h.Content,
h.Type
})
});
foreach (var student in students)
{
Console.WriteLine(student.Name);
foreach (var homework in student.Homework)
{
Console.WriteLine(" -- {0} - {1}",homework.Content,homework.Type);
}
}
}
public static void Seed(StudentContext context)
{
if (context.Students.Count() == 0 && context.Courses.Count() == 0 && context.Resources.Count() == 0 &&
context.Homeworks.Count() == 0)
{
context.Students.Add(new Student()
{
Name = "Haralmpi Zaprianski",
PhoneNumber = "+359 8822341569",
RegistrationDate = DateTime.Now,
Birthday = new DateTime(1996, 7, 22)
});
context.Students.Add(new Student()
{
Name = "Icaka Marinov",
RegistrationDate = DateTime.Now,
Birthday = new DateTime(1999, 3, 3)
});
context.Students.Add(new Student()
{
Name = "Valerian Peshterenski",
PhoneNumber = "+359 8882469485",
RegistrationDate = DateTime.Now,
Birthday = new DateTime(1985, 4, 12)
});
context.SaveChanges();
var studentHaralampi = context.Students.Find(1);
var studentIcaka = context.Students.Find(2);
var studentValerian = context.Students.Find(3);
var cSharpBasics = new Course()
{
Name = "C# Basics",
Description = "Learn to code",
StartDate = DateTime.Now,
EndDate = new DateTime(2015, 8, 2),
Price = 20m
};
var OOP = new Course()
{
Name = "OOP",
Description = "Object-Oriented Programming Fundamentals",
StartDate = DateTime.Now,
EndDate = new DateTime(2015, 1, 18),
Price = 30m
};
var advancedJavaScript = new Course()
{
Name = "Advanced JavaScript",
StartDate = new DateTime(2015, 1, 20),
EndDate = new DateTime(2015, 2, 15),
Price = 20m
};
studentHaralampi.Courses.Add(cSharpBasics);
studentIcaka.Courses.Add(OOP);
studentIcaka.Courses.Add(advancedJavaScript);
studentValerian.Courses.Add(OOP);
studentValerian.Courses.Add(advancedJavaScript);
context.SaveChanges();
var resourceVideoOne = new Resource()
{
Name = "Videa",
ResourceType = ResourceType.Video,
URL = "https://softuni.bg/trainings/1169/Database-Applications-Jul-2015",
CourseId = 1
};
var resourcePresentationOne = new Resource()
{
Name = "Prezentacii",
ResourceType = ResourceType.Presentation,
URL = "https://softuni.bg/trainings/1169/Database-Applications-Jul-2015",
CourseId = 1
};
var resourceVideoTwo = new Resource()
{
Name = "Videa",
ResourceType = ResourceType.Video,
URL = "https://softuni.bg/trainings/1169/Database-Applications-Jul-2015",
CourseId = 2
};
var resourcePresentationTwo = new Resource()
{
Name = "Prezentacii",
ResourceType = ResourceType.Presentation,
URL = "https://softuni.bg/trainings/1169/Database-Applications-Jul-2015",
CourseId = 2
};
var resourceVideoThree = new Resource()
{
Name = "Videa",
ResourceType = ResourceType.Video,
URL = "https://softuni.bg/trainings/1169/Database-Applications-Jul-2015",
CourseId = 3
};
var resourcePresentationThree = new Resource()
{
Name = "Prezentacii",
ResourceType = ResourceType.Presentation,
URL = "https://softuni.bg/trainings/1169/Database-Applications-Jul-2015",
CourseId = 3
};
var cSharpBasicsCourse = context.Courses.Find(1);
var OOPCourse = context.Courses.Find(2);
var advancedJavaScriptCourse = context.Courses.Find(3);
cSharpBasicsCourse.Resources.Add(resourceVideoOne);
cSharpBasicsCourse.Resources.Add(resourcePresentationOne);
OOPCourse.Resources.Add(resourceVideoTwo);
OOPCourse.Resources.Add(resourcePresentationTwo);
advancedJavaScriptCourse.Resources.Add(resourceVideoThree);
advancedJavaScriptCourse.Resources.Add(resourcePresentationThree);
var homeworkOne = new Homework()
{
Content = "Pisna mi",
ContentType = ContentType.ApplicationZip,
StudentId = 1,
CourseId = 1,
SubmissionDate = new DateTime(2015, 7, 23)
};
var homeworkTwo = new Homework()
{
Content = "Pff",
ContentType = ContentType.ApplicationPDF,
StudentId = 2,
CourseId = 2,
SubmissionDate = new DateTime(2015, 6, 15)
};
var homeworkThree = new Homework()
{
Content = "Offff",
ContentType = ContentType.ApplicationZip,
StudentId = 3,
CourseId = 3,
SubmissionDate = new DateTime(2015, 7, 21)
};
cSharpBasicsCourse.Homeworks.Add(homeworkOne);
OOPCourse.Homeworks.Add(homeworkTwo);
advancedJavaScriptCourse.Homeworks.Add(homeworkThree);
context.SaveChanges();
}
}
static void Main(string[] args)
{
var context = new StudentContext();
//Seed(context);
//var homeworks = context.Homeworks
// .Select(h => new
// {
// h.Content,
// h.ContentType,
// StudentName = h.Student.Name
// });
//foreach (var homework in homeworks)
//{
// Console.WriteLine("{0}, {1} - {2}", homework.Content, homework.ContentType.ToString(), homework.StudentName);
//}
//var courses = context.Courses
// .OrderBy(c => c.StartDate)
// .ThenByDescending(c => c.EndDate)
// .Select(c => new
// {
// c.Name,
// c.Description,
// Resources = c.Resources
// });
//foreach (var course in courses)
//{
// Console.WriteLine("{0} - {1}\nResources:", course.Name, course.Description);
// foreach (var resource in course.Resources)
// {
// Console.WriteLine("{0}, {1}, {2}", resource.Name, resource.ResourceType, resource.URL);
// }
// Console.WriteLine();
//}
//var courses = context.Courses
// .Where(c => c.Resources.Count() > 5)
// .OrderByDescending(c => c.Resources.Count)
// .ThenByDescending(c => c.StartDate)
// .Select(c => new
// {
// c.Name,
// ResourceCount = c.Resources.Count
// }).ToList();
//if (courses.Count == 0)
//{
// Console.WriteLine("No course with more than five resources.");
//}
//else
//{
// foreach (var course in courses)
// {
// Console.WriteLine("{0} has {1} resources.", course.Name, course.ResourceCount);
// }
//}
//var courses = context.Courses
// .Where(c => c.StartDate >= new DateTime(2015, 1, 28) && new DateTime(2015, 2, 14) <= c.EndDate)
// .OrderByDescending(c => c.Students.Count)
// .ThenByDescending(c => DbFunctions.DiffDays(c.EndDate, c.StartDate))
// .Select(c => new
// {
// c.Name,
// c.StartDate,
// c.EndDate,
// NumberOfStudents = c.Students.Count
// });
//foreach (var course in courses)
//{
// Console.WriteLine("{0} - {1} {2}, {3}, {4}", course.Name, course.StartDate, course.EndDate,
// course.EndDate - course.StartDate, course.NumberOfStudents);
//}
var students = context.Students
.Select(s => new
{
s.Name,
NumberOfCourses = s.Courses.Count,
TotalPrice = s.Courses.Sum(c => c.Price),
AveragePrice = s.Courses.Average(c => c.Price)
});
foreach (var student in students)
{
Console.WriteLine("{0} - {1}, {2}, {3}",student.Name, student.NumberOfCourses, student.TotalPrice, student.AveragePrice);
}
}
static void Main()
{
var ctx = new StudentContext();
// Task 3
//step 1 - Lists all students and their homework submissions. Select only their
//names and for each homework - content and content-type.
Console.WriteLine("Task 3\n");
var studentswithHomeworks = ctx.Homeworks.Select(h =>
new
{
StudentName = h.Student.Name,
Content = h.Content,
ContType = h.ContentType
}).GroupBy(n => n.StudentName);
foreach (var student in studentswithHomeworks)
{
Console.WriteLine("Student's name: {0}", student.Key);
foreach(var homework in student)
{
Console.WriteLine(" Homework Content: " + homework.Content + " Type: " + homework.ContType);
}
}
Console.WriteLine("\n\n");
//step 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 allCoursesWithResourses = ctx.Courses.OrderBy(c => c.StartDate)
.ThenByDescending(c => c.EndDate)
.Select(c => new
{
CourseName = c.Name,
CourseDescription = c.Description,
Resourcess = c.Resources
});
foreach (var course in allCoursesWithResourses)
{
Console.WriteLine("Course name: " + course.CourseName);
var courseDescription = course.CourseDescription == null ? "nope" : course.CourseDescription;
Console.WriteLine("Course description: " + courseDescription);
if (course.Resourcess.Count() != 0)
foreach (var resource in course.Resourcess)
{
Console.WriteLine("Resource name: {0}, Type: {1}, URL: {2}",
resource.Name, resource.Type, resource.URL);
}
else Console.WriteLine("The course has no resources :) use Bat Google.");
Console.WriteLine();
}
Console.WriteLine("\n\n");
//step 3 - I am using more than 2 instead of more than 5, beckause my data is not enough
//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 coursesWithMoreThan2Resources = ctx.Courses.Where(c => c.Resources.Count >= 2)
.OrderByDescending(c => c.Resources.Count())
.ThenByDescending(c => c.StartDate).Select(c => new
{
c.Name,
ResourcesCount = c.Resources.Count()
});
foreach (var course in coursesWithMoreThan2Resources)
{
Console.WriteLine(course.Name + ": " + course.ResourcesCount);
}
Console.WriteLine("\n\n");
//step 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 givenDate = new DateTime(2001, 3, 5);
var allCoursesThatAreActive = ctx.Courses.Where(c => c.StartDate <= givenDate && c.EndDate >= givenDate)
.Select(c => new
{
CourseName = c.Name,
c.StartDate,
c.EndDate,
CourseDuration = EntityFunctions.DiffMinutes(c.StartDate, c.EndDate),
NumOfStudentsEnroll = c.Students.Count()
}).OrderByDescending(c => c.NumOfStudentsEnroll).ThenByDescending(c => c.CourseDuration);
foreach (var course in allCoursesThatAreActive)
{
Console.WriteLine("Course Name: {0}, Start Date: {1}, End Date {2}, Students Enrolled: {3}, Duration: {4} minutes :)\n",
course.CourseName, String.Format("{0:d/M/yyyy HH:mm:ss}", course.StartDate), String.Format("{0:d/M/yyyy HH:mm:ss}", course.EndDate), course.NumOfStudentsEnroll, course.CourseDuration);
}
Console.WriteLine("\n\n");
//step 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 infoAboutStudents = ctx.Students.Select(s => new
{
s.Name,
NumOFCourses = s.Courses.Count(),
TotalPricePaid = s.Courses.Sum(c => c.Price),
AvgPrice = s.Courses.Average(c => c.Price)
}).OrderByDescending(s => s.TotalPricePaid).ThenByDescending(s => s.AvgPrice);
foreach (var student in infoAboutStudents)
{
Console.WriteLine("Student Name: {0}, Num Courses: {1}, Total Price {2} lv, Avg Price: {3} lv",
student.Name, student.NumOFCourses, student.TotalPricePaid, student.AvgPrice);
}
}
static void Main()
{
using (var context = new StudentContext())
{
//Problem 3. Working with the Database
Console.WriteLine();
Console.WriteLine("1. Lists all students and their homework submissions. Select only their names and for each homework - content and content-type");
Console.WriteLine();
var students = context.Students
.Select(s => new
{
s.Name,
homeworks = s.Homeworks.Select(h => new
{
h.Content,
h.ContentType
})
});
foreach (var student in students)
{
Console.WriteLine("--- Student: {0}", student.Name);
foreach (var homework in student.homeworks)
{
Console.WriteLine("Homework content: {0}, of type: {1}", homework.Content, homework.ContentType);
}
}
Console.WriteLine();
Console.WriteLine("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).");
Console.WriteLine();
var courses = context.Courses
.OrderBy(c => c.StartDate)
.ThenByDescending(c => c.EndDate)
.Select(c => new
{
c.Name,
c.Description,
c.Resources
});
foreach (var course in courses)
{
Console.WriteLine("--- Course: {0}, description: {1}", course.Name, course.Description);
foreach (var resource in course.Resources)
{
Console.WriteLine("{0} - {1}, url: {2}", resource.Name, resource.ResourceType, resource.Url);
}
}
Console.WriteLine();
Console.WriteLine("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.");
Console.WriteLine();
var coursesResources = context.Courses
.Where(c => c.Resources.Count() > 5)
.OrderByDescending(c => c.Resources.Count())
.ThenByDescending(c => c.StartDate)
.Select(c => new
{
c.Name,
ResourceCount = c.Resources.Count()
})
.ToList();
coursesResources.ForEach(Console.WriteLine);
Console.WriteLine();
Console.WriteLine("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).");
Console.WriteLine();
var date = new DateTime(2015, 6, 22);
var activeCouses = context.Courses
.Where(c => c.StartDate < date && c.EndDate >= date)
.ToList()
.OrderByDescending(c => c.Students.Count)
.ThenByDescending(c => (c.EndDate - c.StartDate).TotalDays)
.Select(c => new
{
c.Name,
c.StartDate,
c.EndDate,
StudentCount = c.Students.Count,
CurseDuration = (c.EndDate - c.StartDate).TotalDays
})
.ToList();
activeCouses.ForEach(c =>
{
Console.WriteLine("{0} [{1:dd-MM-yyyy} - {2:dd-MM-yyyy}]: {3} days, {4} students", c.Name,
c.StartDate, c.EndDate, c.CurseDuration, c.StudentCount);
});
Console.WriteLine();
Console.WriteLine("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).");
Console.WriteLine();
var studentCourses = context.Students
.Select(s => new
{
s.Name,
NumberOfCourses = s.Courses.Count(),
TotalPrice = s.Courses.Sum(c => c.Price),
AveragePrice = (double) s.Courses.Sum(c => c.Price)/s.Courses.Count()
})
.OrderByDescending(s => s.TotalPrice)
.ThenByDescending(s => s.NumberOfCourses)
.ThenBy(s => s.Name)
.ToList();
studentCourses.ForEach(Console.WriteLine);
}
}
