I'm implementing a DB where I want to include advanced filter and search with manual paging but I'm having hard time making the back-end method efficient. With large enough entity count (I'm currently testing with 700 entities) the following code often makes the server hang or give internal server error (500) while loading the page.
My tests concluded that the algorithm handles high volume if I remove the sorting and filtering part (left paging only) but I want to try and satisfy all the requirements.
Here is my code in the back-end:
public IQueryable<Movie> GetMovies(int page, int pageSize, out int totalMoviesCount,
string sortOrder, string searchString, string actorName, string genreName)
{
var Movies = from m in _dbContext.Movies
.Include(m => m.MoviesDirectors)
.ThenInclude(ba => ba.Director)
.Include(m => m.MoviesGenres)
.ThenInclude(mg => mg.Genre)
.Include(m => m.MoviesActors)
.ThenInclude(ma => ma.Actor)
select m;
// filter
if (!string.IsNullOrEmpty(searchString))
{
Movies = Movies.Where(m => m.Title.Contains(searchString));
}
//
// .. genre and actor filtering
//order
switch (sortOrder)
{
case "title":
Movies = Movies.OrderBy(m => m.Title);
break;
case "titledesc":
Movies = Movies.OrderByDescending(m => m.Title);
break;
case "year":
Movies = Movies.OrderBy(m => m.PublishDate).ThenBy(m => m.Title);
break;
//
// .. likes, rating and default ordering
}
totalMoviesCount = Movies.Count();
return Movies
.Skip(page * pageSize)
.Take(pageSize);
}
I think it is possible to make it work but I don't know where could be the part I'm missing.
firstly do a needed filtering on _dbContext.Movies. After this filtering you can simply load all other data from other tables. This will make JOINs faster because you will not JOIN every movie but only ones that you are intersted in.
P.S. variables should start with lowercase :)