Filter, order and paging implementation too slow c# entity-framework-core linq


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

            switch (sortOrder)
                case "title":
                    Movies = Movies.OrderBy(m => m.Title);
                case "titledesc":
                    Movies = Movies.OrderByDescending(m => m.Title);
                case "year":
                    Movies = Movies.OrderBy(m => m.PublishDate).ThenBy(m => m.Title);
                    // .. likes, rating and default ordering
            totalMoviesCount = Movies.Count();

            return Movies
             .Skip(page * pageSize)

I think it is possible to make it work but I don't know where could be the part I'm missing.

7/21/2019 8:53:20 AM

Popular Answer

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 :)

7/21/2019 9:01:08 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow