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); }