public async Task <IEnumerable <IEnumerable <Enrollment> > > ListTopThreeStudentsForEachCourse()
{
try
{
using var _context = new CompleteExampleDBContext(_config);
var courseIds = await _context.Courses.Select(x => x.CourseId).Distinct().ToListAsync();
var result = new List <List <Enrollment> >();
foreach (var id in courseIds)
{
result.Add(await _context.Enrollment
.Where(x => x.CourseId == id)
.OrderByDescending(x => x.Grade)
.Take(3)
.Include(x => x.Student)
.Include(x => x.Course)
.ToListAsync());
}
return(result);
}
catch (Exception)
{
//If was a real system a put a log here!
throw;
}
}
public void RunBeforeAnyTests()
{
var config = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
var optionBuilder = new DbContextOptionsBuilder <CompleteExampleDBContext>();
optionBuilder.UseSqlServer(config.GetConnectionString("SchoolContext"));
_context = new CompleteExampleDBContext(optionBuilder.Options);
}
public async Task <Course> GetById(int id)
{
try
{
using var _context = new CompleteExampleDBContext(_config);
return(await _context.Courses.FirstOrDefaultAsync(x => x.CourseId == id));
}
catch (Exception)
{
//If was a real system a put a log here!
throw;
}
}
public async Task <IList <Course> > Get()
{
try
{
using var _context = new CompleteExampleDBContext(_config);
var result = await _context.Courses.Include(x => x.Enrollments).ThenInclude(x => x.Student).ToListAsync();
return(result);
}
catch (Exception)
{
//If was a real system a put a log here!
throw;
}
}
public async Task Update(int id, Enrollment obj)
{
try
{
using var _context = new CompleteExampleDBContext(_config);
_context.Attach(obj);
_context.Entry(obj).Property(p => p.Grade).IsModified = true;
await _context.SaveChangesAsync();
}
catch (Exception)
{
//If was a real system a put a log here!
throw;
}
}
public async Task <IEnumerable <Enrollment> > GetByInstructorId(int instructorId)
{
try
{
using var _context = new CompleteExampleDBContext(_config);
var result = await _context.Enrollment
.Include(x => x.Student)
.Include(x => x.Course)
.ThenInclude(x => x.Instructor)
.Where(x => x.Course.InstructorId == instructorId)
.ToListAsync();
return(result);
}
catch (Exception)
{
//If was a real system a put a log here!
throw;
}
}
public async Task <int> Add(Enrollment obj)
{
try
{
using var _context = new CompleteExampleDBContext(_config);
var enrollment = new Enrollment
{
CourseId = obj.CourseId,
StudentId = obj.StudentId,
};
await _context.Enrollment.AddAsync(obj);
await _context.SaveChangesAsync();
return(obj.EnrollmentId);
}
catch (Exception)
{
//If was a real system a put a log here!
throw;
}
}
public async Task <IEnumerable <Course> > ListCoursesByStudent(int idStudent)
{
try
{
using var _context = new CompleteExampleDBContext(_config);
var idCourses = await _context.Enrollment.Where(x => x.StudentId == idStudent).Select(x => x.CourseId).ToArrayAsync();
var result = new List <Course>();
foreach (var id in idCourses)
{
var course = await _context.Courses.Include(x => x.Instructor).FirstOrDefaultAsync(x => x.CourseId == id);
result.Add(course);
}
return(result.OrderBy(x => x.Title));
}
catch (Exception)
{
//If was a real system a put a log here!
throw;
}
}
