public void AddStudentWithWorkShop(Registration registration)
{
using (var db = new AvansContext())
{
// valideer data. Heeft de student een achternaam? Bestaat de Workshop?
if (!db.Workshops.Any(x => x.Name == registration.Workshop.Name))
{
throw new Exception($"De Workshop {registration.Workshop.Name} bestaat niet. Kies een bestaande workshop");
}
if (string.IsNullOrEmpty(registration.Student.Lastname))
{
throw new Exception("De student dient minimaal een achternaam te hebben");
}
if (string.IsNullOrEmpty(registration.Student.Email) || db.Students.Any(x => x.Email.ToLower() == registration.Student.Email.ToLower()))
{
throw new Exception($"Het emailadres {registration.Student.Email} bestaat al of is leeg.");
}
/* Omdat we een nieuwe AvansContext hebben aangemaakt weet de ChangeTracker niet dat Workshop al aanwezig is in de database.
* Als we niets doen zal EF een Workshop gaan aanmaken en willen opslaan in de database. Dit zorgt voor een error. Welke error verwacht je?
* We dienen daarom de Context te vertellen dat de State van Workshop niet gewijzigd is.
*
* Je kunt ook van een ander principe gebruik maken. Wat zou je ook kunnen doen om dit probleem op te lossen? Probeer dit zelf het uit te zoeken voor dat je het aan de docent vraagt!
*/
db.Entry(registration.Workshop).State = EntityState.Unchanged;
// Nu alle checks en states zijn gezet kan de Registratie worden gekoppeld aan de context. EF zorgt voor het aanmaken van de data in de diverse tabellen
db.Registrations.Add(registration);
db.SaveChanges();
}
}
public List <Student> GetStudents()
{
using (var db = new AvansContext())
{
return(new List <Student>()); // todo: return students
}
}
public List <Student> GetStudents()
{
using (var db = new AvansContext())
{
return(db.Students.ToList());
}
}
public bool SwapTeachers(int teacherFrom, int teacherTo)
{
using (var db = new AvansContext())
{
// teachers moeten wel bestaan natuurlijk
var fromTeacher = db.Teachers.Include(x => x.Workshops).SingleOrDefault(x => x.Id == teacherFrom);
var toTeacher = db.Teachers.Include(x => x.Workshops).SingleOrDefault(x => x.Id == teacherTo);
if (fromTeacher == null || toTeacher == null)
{
return(false);
}
// Kan dit eenvoudiger?
var workshopsFrom = fromTeacher.Workshops;
var workshopsTo = toTeacher.Workshops;
foreach (var wsf in workshopsFrom)
{
wsf.Teachers.Add(toTeacher);
wsf.Teachers.Remove(fromTeacher);
}
foreach (var wst in workshopsTo)
{
wst.Teachers.Remove(toTeacher);
wst.Teachers.Add(fromTeacher);
}
return(db.SaveChanges() > 0);
}
}
public List <Workshop> GetWorkshops()
{
using (var db = new AvansContext())
{
return(db.Workshops.ToList());
}
}
public Tuple <int, int> StudentenMetEnZonderRegistratie()
{
using (var db = new AvansContext())
{
var studentWithReg = db.Students.Where(x => x.Registrations.Any()).Count();
var studentWithoutReg = db.Students.Where(x => !x.Registrations.Any()).Count();
return(new Tuple <int, int>(studentWithReg, studentWithoutReg));
}
}
private void AddOrUpdateTeacher(AvansContext context, string workshopName, string teacherName)
{
var workshop = context.Workshops.SingleOrDefault(c => c.Name == workshopName);
var assignedTeacher = workshop.Teachers.SingleOrDefault(i => i.Lastname == teacherName);
if (assignedTeacher == null)
{
workshop.Teachers.Add(context.Teachers.Single(i => i.Lastname == teacherName));
}
}
public List <Workshop> WorkshopInformation()
{
using (var db = new AvansContext())
{
// Probeer eens te achterhalen welke query EF daadwerkelijk naar SQL Server stuurt?
return(db.Workshops
.Include(r => r.Registrations.Select(s => s.Student))
.Include(t => t.Teachers)
.ToList());
}
}
public int DeleteWorkshopWithoutRegistration()
{
using (var db = new AvansContext())
{
var wsNoReg = from ws in db.Workshops
where !ws.Registrations.Any()
select ws;
var numWorkshops = wsNoReg.Count();
db.Workshops.RemoveRange(wsNoReg);
db.SaveChanges();
return(numWorkshops);
}
}
public List <Department> BudgetIncrease(int increaseBy)
{
using (var db = new AvansContext())
{
int percentage = 100 + increaseBy;
// Haal de workshops op met meer dan 2 registraties
var workshopDepartments = db.Workshops.Where(w => w.Registrations.Count > 2).Select(x => x.DepartmentId);
// We hebben een lijstje met DeparmentId's. Dit lijstje kun je gebruiken om te bepalen of een ID van Department er in voorkomt.
var departments = db.Departments.Where(x => workshopDepartments.Contains(x.Id));
foreach (var dp in departments)
{
dp.Budget = dp.Budget * percentage / 100;
}
db.SaveChanges();
return(departments.ToList());
}
}
protected override void Seed(AvansContext context)
{
var students = new List <Student>
{
new Student {
Firstname = "Jan", Lastname = "Jansen", Email = "*****@*****.**"
},
new Student {
Firstname = "Kees", Lastname = "Kist", Email = "*****@*****.**"
},
new Student {
Firstname = "Piet", Lastname = "Bakker", Email = "*****@*****.**"
},
new Student {
Firstname = "S1", Lastname = "Student1", Email = "*****@*****.**"
},
new Student {
Firstname = "S2", Lastname = "Student2", Email = "*****@*****.**"
},
new Student {
Firstname = "S3", Lastname = "Student3", Email = "*****@*****.**"
},
new Student {
Firstname = "S4", Lastname = "Student4", Email = "*****@*****.**"
},
new Student {
Firstname = "S5", Lastname = "Student5", Email = "*****@*****.**"
},
new Student {
Firstname = "S6", Lastname = "Student6", Email = "*****@*****.**"
},
new Student {
Firstname = "S7", Lastname = "Student7", Email = "*****@*****.**"
},
};
students.ForEach(s => context.Students.AddOrUpdate(s));
context.SaveChanges();
var teachers = new List <Teacher>
{
new Teacher {
Firstname = "Ger", Lastname = "Saris", Email = "*****@*****.**"
},
new Teacher {
Firstname = "Stijn", Lastname = "Smulders", Email = "*****@*****.**"
},
new Teacher {
Firstname = "Ronald", Lastname = "Schellekens", Email = "*****@*****.**"
},
};
teachers.ForEach(t => context.Teachers.AddOrUpdate(t));
context.SaveChanges();
var departments = new List <Department>
{
new Department {
DepartmentCode = "JAV", Name = "Java", Budget = 100000, TeacherId = teachers.Single(t => t.Lastname == "Saris").Id
},
new Department {
DepartmentCode = "NET", Name = ".Net", Budget = 325000, TeacherId = teachers.Single(t => t.Lastname == "Schellekens").Id
},
new Department {
DepartmentCode = "WEB", Name = "Web", Budget = 5000, TeacherId = teachers.Single(t => t.Lastname == "Smulders").Id
},
new Department {
DepartmentCode = "INT", Name = "Introductie", Budget = 92000, TeacherId = teachers.Single(t => t.Lastname == "Smulders").Id
},
new Department {
DepartmentCode = "ALG", Name = "Algemeen", Budget = 0, TeacherId = teachers.Single(t => t.Lastname == "Schellekens").Id
},
};
departments.ForEach(d => context.Departments.AddOrUpdate(d));
context.SaveChanges();
var workshops = new List <Workshop>
{
new Workshop {
Name = "PROG5",
Summary = "Leren programmeren in C#",
DepartmentId = departments.Single(s => s.DepartmentCode == "NET").Id,
Teachers = new List <Teacher>()
},
new Workshop {
Name = "PROG4",
Summary = "Leren programmeren in Java",
DepartmentId = departments.Single(s => s.DepartmentCode == "JAV").Id,
Teachers = new List <Teacher>()
},
new Workshop {
Name = "WEBS1",
Summary = "Leren programmeren in HTML & CSS",
DepartmentId = departments.Single(s => s.DepartmentCode == "WEB").Id,
Teachers = new List <Teacher>()
},
new Workshop {
Name = "Workshop1",
Summary = "Workshop 1",
DepartmentId = departments.Single(s => s.DepartmentCode == "ALG").Id,
Teachers = new List <Teacher>()
},
new Workshop {
Name = "Workshop2",
Summary = "Workshop 2",
DepartmentId = departments.Single(s => s.DepartmentCode == "ALG").Id,
Teachers = new List <Teacher>()
},
new Workshop {
Name = "Workshop3",
Summary = "Workshop 3",
DepartmentId = departments.Single(s => s.DepartmentCode == "ALG").Id,
Teachers = new List <Teacher>()
},
};
workshops.ForEach(w => context.Workshops.AddOrUpdate(w));
context.SaveChanges();
// Registration toevoegen
var registrations = new List <Registration>
{
new Registration {
StudentId = students.Single(s => s.Firstname == "Jan").Id,
WorkshopId = workshops.Single(w => w.Name == "PROG5").Id,
Grade = Grade.Goed
},
new Registration {
StudentId = students.Single(s => s.Firstname == "Jan").Id,
WorkshopId = workshops.Single(w => w.Name == "PROG4").Id,
Grade = Grade.Voldaan
},
new Registration {
StudentId = students.Single(s => s.Firstname == "Jan").Id,
WorkshopId = workshops.Single(w => w.Name == "WEBS1").Id,
Grade = Grade.NietVoldaan
},
new Registration {
StudentId = students.Single(s => s.Firstname == "Kees").Id,
WorkshopId = workshops.Single(w => w.Name == "PROG5").Id,
Grade = Grade.NietVoldaan
},
new Registration {
StudentId = students.Single(s => s.Firstname == "Kees").Id,
WorkshopId = workshops.Single(w => w.Name == "PROG4").Id,
Grade = Grade.NietVoldaan
},
new Registration {
StudentId = students.Single(s => s.Firstname == "Piet").Id,
WorkshopId = workshops.Single(w => w.Name == "PROG5").Id
},
new Registration {
StudentId = students.Single(s => s.Firstname == "Piet").Id,
WorkshopId = workshops.Single(w => w.Name == "WEBS1").Id
},
};
AddOrUpdateTeacher(context, "PROG5", "Schellekens");
AddOrUpdateTeacher(context, "WEBS1", "Smulders");
AddOrUpdateTeacher(context, "PROG4", "Saris");
foreach (Registration r in registrations)
{
var bewaren = context.Registrations.Where(
s => s.Student.Id == r.StudentId &&
s.Workshop.Id == r.WorkshopId).SingleOrDefault();
if (bewaren == null)
{
context.Registrations.Add(r);
}
}
context.SaveChanges();
}
