public async Task <ListViewModel <CoursesViewModel> > GetCoursesAsync(CoursesListInputModel input)
{
logger.LogInformation("Course richiesti");
string orderBy = input.OrderBy == "CurrentPrice" ? "CurrentPrice_Amount" : input.OrderBy;
string direction = input.Ascending ? "ASC" : "DESC";
FormattableString query = $@"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Title LIKE {"%"+input.Search+"%"} ORDER BY {(Sql) orderBy} {(Sql) direction} LIMIT {input.Limit} OFFSET {input.Offset};
SELECT COUNT(*) FROM Courses WHERE Title LIKE {"%"+input.Search+"%"}";
DataSet dataSet = await db.QueryAsync(query);
var dataTable = dataSet.Tables[0];
var CoursesList = new List <CoursesViewModel>();
foreach (DataRow item in dataTable.Rows)
{
CoursesViewModel corsi = CoursesViewModel.FromDataRow(item);
CoursesList.Add(corsi);
}
int totalCount = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]);
ListViewModel <CoursesViewModel> result = new ListViewModel <CoursesViewModel>
{
Result = CoursesList,
TotalCount = totalCount
};
return(result);
}
public async Task <IActionResult> Index(CoursesListInputModel inputModel)
{
ListViewModel <CoursesViewModel> corsi = await courseService.GetCoursesAsync(inputModel);
CourseListViewModel courseListViewModel = new CourseListViewModel
{
Course = corsi,
Input = inputModel
};
ViewData["Title"] = "Catalogo corsi";
return(View(courseListViewModel));
}
public Task BindModelAsync(ModelBindingContext bindingContext)
{
// Recuperiamo i valori grazie ai value provider
string search = bindingContext.ValueProvider.GetValue("Search").FirstValue;
string orderBy = bindingContext.ValueProvider.GetValue("OrderBy").FirstValue;
int.TryParse(bindingContext.ValueProvider.GetValue("page").FirstValue, out int page);
bool.TryParse(bindingContext.ValueProvider.GetValue("ascending").FirstValue, out bool ascending);
// Creiamo l'istanza di CoursesListInputModel
var option = courseOption.CurrentValue;
var inputModel = new CoursesListInputModel(search, page, orderBy, ascending, option.PerPagina, option.Order);
// Impostiamo il risultato per notificare che la conversione è avvenuta con successo
bindingContext.Result = ModelBindingResult.Success(inputModel);
// Restituiamo un Task completato
return(Task.CompletedTask);
}
public Task <ListViewModel <CoursesViewModel> > GetCoursesAsync(CoursesListInputModel model)
{
// Vengono messe in cache solo le prime 5 pagine in quanto sono le più visitate e viene sfruttata la cache solo se l'utente non ha cercato nulla
bool canCache = model.Page <= 5 && string.IsNullOrWhiteSpace(model.Search);
// Se canCdouble cachedMaxtime = configurationOption.GetSection("CachedTime").GetValue<double>("TimeSpanCourse");ache è true, usa il servizio di caching
if (canCache)
{
double cachedMaxtime = configurationOption.GetSection("CachedTime").GetValue <double>("TimeSpanCourse");
return(memoryCache.GetOrCreateAsync($"Course {model.Page}-{model.OrderBy}-{model.Ascending}", cacheEntry =>
{
//cacheEntry.SetSize(1);
cacheEntry.SetAbsoluteExpiration(TimeSpan.FromSeconds(cachedMaxtime));
return courseService.GetCoursesAsync(model);
}));
}
// Se canCache è false, usa direttamente l'applicazione di servizio
return(courseService.GetCoursesAsync(model));
}
public Task <ListViewModel <CoursesViewModel> > GetCoursesAsync(CoursesListInputModel model)
{
throw new NotImplementedException();
}
public async Task <ListViewModel <CoursesViewModel> > GetCoursesAsync(CoursesListInputModel input)
{
IQueryable <Course> baseQuery = dbContext.Courses;
switch (input.OrderBy)
{
case "Title":
if (input.Ascending)
{
baseQuery = baseQuery.OrderBy(course => course.Title);
}
else
{
baseQuery = baseQuery.OrderByDescending(course => course.Title);
}
break;
case "Rating":
if (input.Ascending)
{
baseQuery = baseQuery.OrderBy(course => course.Rating);
}
else
{
baseQuery = baseQuery.OrderByDescending(course => course.Rating);
}
break;
case "CurrentPrice":
if (input.Ascending)
{
baseQuery = baseQuery.OrderBy(course => course.CurrentPrice.Amount);
}
else
{
baseQuery = baseQuery.OrderByDescending(course => course.CurrentPrice.Amount);
}
break;
}
IQueryable <CoursesViewModel> queryLinq = baseQuery
.Where(course => EF.Functions.Like(course.Title, $"%{input.Search}%")) //course.Title.Contains(input.Search)) now is case-insensitive
.AsNoTracking()
.Select(course =>
new CoursesViewModel {
Id = course.Id,
Title = course.Title,
Author = course.Author,
ImagePath = course.ImagePath,
Rating = course.Rating,
FullPrice = course.FullPrice,
CurrentPrice = course.CurrentPrice
});
List <CoursesViewModel> corsi = await queryLinq
.Skip(input.Offset)
.Take(input.Limit)
.ToListAsync(); //Questo è il punto dove la querylink viene eseguita
int totalCount = await queryLinq.CountAsync();
ListViewModel <CoursesViewModel> result = new ListViewModel <CoursesViewModel>();
result.Result = corsi;
result.TotalCount = totalCount;
return(result);
}
