public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
string orderby = model.OrderBy == "CurrentPrice" ? "CurrentPrice_Amount" : model.OrderBy;
string direction = model.Ascending ? "ASC" : "DESC";
FormattableString query = $@"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Title LIKE {"%" + model.Search + "%"} ORDER BY {(Sql) orderby} {(Sql) direction} LIMIT {model.Limit} OFFSET {model.Offset};
SELECT COUNT(*) FROM Courses WHERE Title LIKE {"%" + model.Search + "%"}";
DataSet dataSet = await db.QueryAsync(query);
var dataTable = dataSet.Tables[0];
var courseList = new List <CourseViewModel>();
foreach (DataRow courseRow in dataTable.Rows)
{
CourseViewModel courseViewModel = CourseViewModel.FromDataRow(courseRow);
courseList.Add(courseViewModel);
}
ListViewModel <CourseViewModel> result = new ListViewModel <CourseViewModel>
{
Results = courseList,
TotalCount = Convert.ToInt32(dataSet.Tables[1].Rows[0][0])
};
return(result);
}
public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
string orderBy = model.OrderBy == "CurrentPrice" ? "CurrentPrice_Amount" : model.OrderBy;
string direction = model.Ascending ? "ASC" : "DESC"; //se é true restituisce ASC, se é false: DESC
//quali informazioni estrarre nei confronti di un database? eseguo una query
FormattableString query = $@"SELECT Id, Title, ImagePath, Author, Rating, FullPrice_Amount, FullPrice_Currency, CurrentPrice_Amount, CurrentPrice_Currency FROM Courses WHERE Title LIKE {"%" + model.Search + "%"} ORDER BY {(Sql) orderBy} {(Sql) direction} LIMIT {model.Limit} OFFSET {model.Offset};
SELECT COUNT(*) FROM Courses WHERE Title LIKE {"%" + model.Search + "%"}";
DataSet dataSet = await db.ExecuteQueryAsync(query);
var dataTable = dataSet.Tables[0]; //primo datatable
var courseList = new List <CourseViewModel>(); //aggiungo CourseViewModel ad una lista
foreach (DataRow courseRow in dataTable.Rows) //il datatable contiene tutte le righe trovate e cicliamo tutte le Rows in un foreach
{
CourseViewModel courseViewModel = CourseViewModel.FromDataRow(courseRow); //il metodo FromDataRow conterrà tutti i risultati ciclati e viene richamato. Il tutto mi fa ottenere un oggetto di tipo CourseViewModel
courseList.Add(courseViewModel); //ogni oggetto ciclato viene aggiunto alla lista
}
//creo un istanza dell'oggetto
ListViewModel <CourseViewModel> result = new ListViewModel <CourseViewModel>
{
Results = courseList, //rappresenta l'elenco dei corsi (paginato a 10 corsi per pagina)
TotalCount = Convert.ToInt32(dataSet.Tables[1].Rows[0][0]) //leggo il secondo dataset [1] dove viene eseguita la seconda query e della prima riga ottengo i valore della prima colonna, totale di tutti i corsi
};
return(result);
}
public async Task <IActionResult> Index(CourseListInputModel input)
{
ViewData["Title"] = "I Corsi";
ListViewModel <CourseViewModel> courses = await courseService.GetCoursesAsync(input.Search, input.Page, input.OrderBy, input.Ascending, input.Limit, input.Offset);
CourseListViewModel viewModel = new CourseListViewModel();
viewModel.Courses = courses;
viewModel.Input = input;
return(View(viewModel));
}
public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
IQueryable <Course> baseQuery = dbContext.Courses;
baseQuery = (model.OrderBy, model.Ascending) switch
{
("Title", true) => baseQuery.OrderBy(course => course.Title),
("Title", false) => baseQuery.OrderByDescending(course => course.Title),
("Rating", true) => baseQuery.OrderBy(course => course.Rating),
("Rating", false) => baseQuery.OrderByDescending(course => course.Rating),
("CurrentPrice", true) => baseQuery.OrderBy(course => course.CurrentPrice.Amount),
("CurrentPrice", false) => baseQuery.OrderByDescending(course => course.CurrentPrice.Amount),
("Id", true) => baseQuery.OrderBy(course => course.Id),
("Id", false) => baseQuery.OrderByDescending(course => course.Id),
_ => baseQuery
};
//per ogni proprietà trovata nel CourseViewModel, dobbiamo assegnare il valore trovato nell'entità course
IQueryable <Course> queryLinq = baseQuery
.Where(course => course.Title.Contains(model.Search)) //che contiene il valore di search (ciò che cerca l'utente)
.AsNoTracking();
List <CourseViewModel> courses = await queryLinq //vogliamo ottenere la lista dei corsi (skip e take agiscono qui)
.Skip(model.Offset)
.Take(model.Limit)
.Select(course => CourseViewModel.FromEntity(course))
/*.Select(course =>
* new CourseViewModel
* {
* Id = course.Id,
* Title = course.Title,
* ImagePath = course.ImagePath,
* Author = course.Author,
* Rating = course.Rating,
* CurrentPrice = course.CurrentPrice,
* FullPrice = course.FullPrice
* })*/
.ToListAsync(); //invoco IQueryable ad una List di CourseViewModel, EFC apre la connessione con il Db per inviare la query
int totalCount = await queryLinq.CountAsync(); //Conteggio di tutti i corsi esistenti
//creo un istanza dell'oggetto
ListViewModel <CourseViewModel> result = new ListViewModel <CourseViewModel>
{
Results = courses, //contiene la lista dei corsi (paginata a 10 corsi per pagina)
TotalCount = totalCount
};
return(result);
}
public async Task <IActionResult> Index(CourseListInputModel input)
{
ViewData["Title"] = "Catalogo dei corsi";
ListViewModel <CourseViewModel> courses = await courseService.GetCoursesAsync(input);
CourseListViewModel viewModel = new CourseListViewModel
{
Courses = courses,
Input = input
};
return(View(viewModel));
}
public Task BindModelAsync(ModelBindingContext bindingContext)
{
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);
var inputModel = new CourseListInputModel(search, page, orderby, ascending, courseOptions.CurrentValue);
bindingContext.Result = ModelBindingResult.Success(inputModel);
return(Task.CompletedTask);
}
public async Task <List <CourseViewModel> > GetMostRecentCoursesAsync()
{
CourseListInputModel inputModel = new CourseListInputModel(
search: "",
page: 1,
orderby: "Id",
ascending: false,
limit: coursesOptions.CurrentValue.InHome,
orderOptions: coursesOptions.CurrentValue.Order);
ListViewModel <CourseViewModel> result = await GetCoursesAsync(inputModel);
return(result.Results);
}
//----------------------------------------------------------------------
//I due metodi rappresentano valori preimpostati e non manipolabili dall'utente
public async Task <List <CourseViewModel> > GetBestRatingCoursesAsync()
{
CourseListInputModel inputModel = new CourseListInputModel( //creo l'istanza
search: "",
page: 1,
orderBy: "Rating",
ascending: false,
limit: coursesOptions.CurrentValue.InHome, //rappresenta il totale dei corsi che verranno visualizzati (definito in appsettings.json)
orderOptions: coursesOptions.CurrentValue.Order);
//e la fornisco al metodo GetCourseAsync
ListViewModel <CourseViewModel> result = await GetCoursesAsync(inputModel);
return(result.Results);
}
public async Task <IActionResult> Index(CourseListInputModel input) //Courses
{
ViewData["Title"] = "Catalogo dei corsi"; //titolo statico
//invochiamo il suo metodo GetCourses per ottenere l'elenco dei corsi dal CourseViewModel
ListViewModel <CourseViewModel> courses = await courseService.GetCoursesAsync(input);
CourseListViewModel viewModel = new CourseListViewModel
{
Courses = courses, //valorizziamo i dati come ci sono stati restituiti dal servizio applicativo (contiene i risultati)
Input = input //l'input ricevuto dall'utente arriverà anche alla view
};
return(View(viewModel)); //va a cercare una view chiamata Index.cshtml in courses e gli passa i dati ottenuti tramite metodo
}
public Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
double time = expTime.CurrentValue.Default;
bool canCache = model.Page <= 5 && string.IsNullOrEmpty(model.Search);
if (canCache)
{
return(memoryCache.GetOrCreateAsync($"Courses{model.Search}-{model.Page}-{model.OrderBy}-{model.Ascending}", cacheEntry =>
{
//cacheEntry.SetSize(1);
cacheEntry.SetAbsoluteExpiration(TimeSpan.FromSeconds(time));
return courseService.GetCoursesAsync(model);
}));
}
return(courseService.GetCoursesAsync(model));
}
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 del CourseListInputModel
CoursesOptions options = coursesOptions.CurrentValue;
var inputModel = new CourseListInputModel(search, page, orderBy, ascending, options.PerPage, options.Order);
//Impostiamo il risultato per notificare che la creazione è avvenuta con successo
bindingContext.Result = ModelBindingResult.Success(inputModel);
//Restituiamo un task completato
return(Task.CompletedTask);
}
public Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
//Metto in cache i risultati solo per le prime 5 pagine del catalogo, che reputo essere
//le più visitate dagli utenti, e che perciò mi permettono di avere il maggior beneficio dalla cache.
//E inoltre, metto in cache i risultati solo se l'utente non ha cercato nulla.
//In questo modo riduco drasticamente il consumo di memoria RAM
bool canCache = model.Page <= 5 && string.IsNullOrEmpty(model.Search);
//Se canCache è true, sfrutto il meccanismo di caching
if (canCache)
{
return(memoryCache.GetOrCreateAsync($"Courses{model.Page}-{model.OrderBy}-{model.Ascending}", cacheEntry =>
{
cacheEntry.SetAbsoluteExpiration(TimeSpan.FromSeconds(60));
return courseService.GetCoursesAsync(model);
}));
}
//Altrimenti uso il servizio applicativo sottostante, che recupererà sempre i valori dal database
return(courseService.GetCoursesAsync(model));
}
public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
IQueryable <Course> baseQuery = dbContext.Courses;
baseQuery = (model.OrderBy, model.Ascending) switch
{
("Title", true) => baseQuery.OrderBy(course => course.Title),
("Title", false) => baseQuery.OrderByDescending(course => course.Title),
("Rating", true) => baseQuery.OrderBy(course => course.Rating),
("Rating", false) => baseQuery.OrderByDescending(course => course.Rating),
("CurrentPrice", true) => baseQuery.OrderBy(course => course.CurrentPrice.Amount),
("CurrentPrice", false) => baseQuery.OrderByDescending(course => course.CurrentPrice.Amount),
("Id", true) => baseQuery.OrderBy(course => course.Id),
("Id", false) => baseQuery.OrderByDescending(course => course.Id),
_ => baseQuery
};
IQueryable <Course> queryLinq = baseQuery
.Where(course => course.Title.Contains(model.Search))
.AsNoTracking();
List <CourseViewModel> courses = await queryLinq
.Skip(model.Offset)
.Take(model.Limit)
.Select(course => CourseViewModel.FromEntity(course)) //Usando metodi statici come FromEntity, la query potrebbe essere inefficiente. Mantenere il mapping nella lambda oppure usare un extension method personalizzato
.ToListAsync(); //La query al database viene inviata qui, quando manifestiamo l'intenzione di voler leggere i risultati
int totalCount = await queryLinq.CountAsync();
ListViewModel <CourseViewModel> result = new ListViewModel <CourseViewModel>
{
Results = courses,
TotalCount = totalCount
};
return(result);
}
public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
IQueryable <Course> baseQuery = dbContext.Courses;
baseQuery = (model.OrderBy, model.Ascending) switch
{
("Title", true) => baseQuery.OrderBy(course => course.Title),
("Title", false) => baseQuery.OrderByDescending(course => course.Title),
("Rating", true) => baseQuery.OrderBy(course => course.Rating),
("Rating", false) => baseQuery.OrderByDescending(course => course.Rating),
("CurrentPrice", true) => baseQuery.OrderBy(course => course.CurrentPrice.Amount),
("CurrentPrice", false) => baseQuery.OrderByDescending(course => course.CurrentPrice.Amount),
("Id", true) => baseQuery.OrderBy(course => course.Id),
("Id", false) => baseQuery.OrderByDescending(course => course.Id),
_ => baseQuery
};
IQueryable <Course> queryLinq = baseQuery
.Where(course => course.Title.Contains(model.Search))
.AsNoTracking();
List <CourseViewModel> courses = await queryLinq
.Skip(model.Offset)
.Take(model.Limit)
.Select(course => CourseViewModel.FromEntity(course))
.ToListAsync();
int totalCount = await queryLinq.CountAsync();
ListViewModel <CourseViewModel> result = new()
{
Results = courses,
TotalCount = totalCount
};
return(result);
}
public async Task <ListViewModel <CourseViewModel> > GetCoursesAsync(CourseListInputModel model)
{
IQueryable <Course> baseQuery = dbContext.Courses;
switch (model.OrderBy)
{
case "Title":
if (model.Ascending)
{
baseQuery = baseQuery.OrderBy(course => course.Title);
}
else
{
baseQuery = baseQuery.OrderByDescending(course => course.Title);
}
break;
case "Rating":
if (model.Ascending)
{
baseQuery = baseQuery.OrderBy(course => course.Rating);
}
else
{
baseQuery = baseQuery.OrderByDescending(course => course.Rating);
}
break;
case "CurrentPrice":
if (model.Ascending)
{
baseQuery = baseQuery.OrderBy(course => course.CurrentPrice.Amount);
}
else
{
baseQuery = baseQuery.OrderByDescending(course => course.CurrentPrice.Amount);
}
break;
}
IQueryable <CourseViewModel> queryLinq = baseQuery
//.Where(course => course.Title.Contains(model.Search))
.Where(course => EF.Functions.Like(course.Title, $"%{model.Search}%"))
.AsNoTracking()
.Select(course =>
new CourseViewModel
{
Id = course.Id,
Title = course.Title,
ImagePath = course.ImagePath,
Author = course.Author,
Rating = course.Rating,
CurrentPrice = course.CurrentPrice,
FullPrice = course.FullPrice
});
List <CourseViewModel> courses = await queryLinq
.Skip(model.Offset)
.Take(model.Limit)
.ToListAsync();
int totalCount = await queryLinq.CountAsync();
ListViewModel <CourseViewModel> results = new ListViewModel <CourseViewModel>
{
Results = courses,
TotalCount = totalCount
};
return(results);
}
