For every joke that has an author, the author will be loaded. An author is retrieved n times because we don't cache the author since we are using .AsNoTracking
We load the whole user entity event though we only want the username
The solution in JokesRepository
Inside GetJokeDtosAsync we know that this will return a JokeReplyDto which has an author entity, that means we can directly populate this here.
We can start defining the query var query = _context.Jokes.AsNoTracking(); // asnotracking will make it a queryable
We do our filters on the query such has if (!string.IsNullOrWhiteSpace(jokesFilter.AuthorId)) { query = query.Where(joke => joke.AuthorId == jokesFilter.AuthorId); }
We paginate the query which means we need to refactor our PaginatedList to return the iqueryable instead of executing it so we later on can execute the .ToPaginatedList var jokes = await query.Paginate(int pageSize)
Now we need to load the relatedAuthors and we can use the query to only make one request to the db by doing so we are making a SELECTsubquery instead of a Joinwhich becomes more performant. var authors = await _context.Users.Where(u => query.Select(j => j.AuthorId).Contains(u.Id)) .Select(x => new { x.UserName, x.Id }).ToListAsync()
Almost done, just need to create the ReplyDto return jokes.Select(j => new JokeReplyDto { Author = new JokeAuthorDto { Id = j.AuthorId, UserName = authors.FirstOrDefault(x => x.Id == j.AuthorId).UserName }, });
What have we changed?
We only load the data necessary by doing multiple Select statements
We make a query, filter, paginate and use this query in a query where we want to get related data. That means we always only make 2 Queries to the database
The problem in JokeService
The solution in JokesRepository
var query = _context.Jokes.AsNoTracking(); // asnotracking will make it a queryableif (!string.IsNullOrWhiteSpace(jokesFilter.AuthorId)) { query = query.Where(joke => joke.AuthorId == jokesFilter.AuthorId); }var jokes = await query.Paginate(int pageSize)SELECTsubquery instead of aJoinwhich becomes more performant.var authors = await _context.Users.Where(u => query.Select(j => j.AuthorId).Contains(u.Id)) .Select(x => new { x.UserName, x.Id }).ToListAsync()return jokes.Select(j => new JokeReplyDto { Author = new JokeAuthorDto { Id = j.AuthorId, UserName = authors.FirstOrDefault(x => x.Id == j.AuthorId).UserName }, });What have we changed?