Question posted in
The official documentation can be found here.

Asp.net – Making retrieving data from database faster

last time L had that type of retrieving data:

var users = _context.Users.AsQueryable();


var userDtos = users.Select(user => new UserGetDto
{
    Id = user.Id,
    Name = user.Name,
    SurName = user.SurName,
    Age = user.Age,
    Faculty = user.Faculty != null
              ? new FacultyOnlyDto
              {
                  Id = user.Faculty.Id,
                  FacultyName = user.Faculty.FacultyName
              }
              : null,
    Courses = user.UsersCourses != null
              ? user.UsersCourses.Where(uc => uc.Course != null).Select(uc => new CourseOnlyDto
              {
                  Id = uc.Course.Id,
                  CourseName = uc.Course.CourseName
              }).ToList()
              : new List<CourseOnlyDto>(),
    Lecturers = user.UsersLecturers != null
                ? user.UsersLecturers.Where(ul => ul.Lecturer != null).Select(ul => new LecturerOnlyDto
                {
                    Id = ul.Lecturer.Id,
                    Name = ul.Lecturer.Name,
                    SurName = ul.Lecturer.SurName,
                    Age = ul.Lecturer.Age
                }).ToList()
                : new List<LecturerOnlyDto>()
}).ToList();

but it was super slow, than I tried to faster it up using that LINQ query:

var users = _context.Users.AsQueryable();
var userLecturers = _context.UsersLecturersJoin.AsQueryable();
var lecturers = _context.Lecturers.AsQueryable();
var userCourses = _context.UsersCoursesJoin.AsQueryable();
var courses = _context.Courses.AsQueryable();
var faculties = _context.Faculty.AsQueryable();

var query = from u in users
            join uc in userCourses on u.Id equals uc.UserId into joinedUserCourses
            from ucs in joinedUserCourses.DefaultIfEmpty()
            join ul in userLecturers on u.Id equals ul.UserId into joinedUserLecturers
            from uls in joinedUserLecturers.DefaultIfEmpty()
            join f in faculties on u.FacultyId equals f.Id into joinedFaculties
            from fs in joinedFaculties.DefaultIfEmpty()
            group new { u, ucs, uls, fs } by new
            {
                u.Id, u.Name, u.SurName, u.Age,
                FacultyId = fs.Id, fs.FacultyName,
                ucs.CourseId,
                uls.LecturerId
            }
            into groupedResult
            select new UserGetDto
            {
                Id = groupedResult.Key.Id,
                Name = groupedResult.Key.Name,
                SurName = groupedResult.Key.SurName,
                Age = groupedResult.Key.Age,
                Faculty = new FacultyOnlyDto
                {
                    Id = groupedResult.Key.FacultyId,
                    FacultyName = groupedResult.Key.FacultyName
                },
                Courses = (from res in groupedResult
                           join c in courses on res.ucs.CourseId equals c.Id into joinedCourses
                           from cs in joinedCourses.DefaultIfEmpty()
                           select new CourseOnlyDto
                           {
                               Id = cs.Id,
                               CourseName = cs.CourseName
                           }),
                Lecturers = (from res in groupedResult
                             join l in lecturers on res.uls.LecturerId equals l.Id into joinedLecturers
                             from ls in joinedLecturers.DefaultIfEmpty()
                             select new LecturerOnlyDto
                             {
                                 Id = ls.Id,
                                 Name = ls.Name,
                                 SurName = ls.SurName,
                                 Age = ls.Age
                             })
            };
var res = query.ToList();

That last method gives me subqueries in main query and I think that subqueries are not neccessary and it can be simpified, but I don’t know how 🙂 Any tips? Should I use dapper to faster it or i don’t have to?

Tags:

2

Answers


  1. 0

    Despite everything else what was mentioned before you can try couple of more things.

    1. Try split query strategy. If you have small number of courses and lecturers but big number of students – then It can bring some performance improvements. Has to be measured though. Split query is available from EF Core 7 I believe, see if it is applicable for you.
    2. If you are selecting the data just for returning without further modifications – consider to use AsNoTracking method to skip the change tracker.
    3. I don’t see the Include methods in your LINQ. Might be that your are using lazy loading approach. If so – you have a N+1 issue here. Investigate the generated SQL.
    Login or Signup to reply.
  2. 0

    I have changed important parts of the query. You don’t have to check collections for nulls and AsSplitQuery for improving Eager Loading.

    var users = _context.Users.AsQueryable();

var userDtos = users.Select(user => new UserGetDto
    {
        Id = user.Id,
        Name = user.Name,
        SurName = user.SurName,
        Age = user.Age,
        Faculty = user.Faculty != null
                ? new FacultyOnlyDto
                {
                    Id = user.Faculty.Id,
                    FacultyName = user.Faculty.FacultyName
                }
                : null,
        Courses = user.UsersCourses
                    .Where(uc => uc.Course != null)
                    .Select(uc => new CourseOnlyDto
                    {
                        Id = uc.Course.Id,
                        CourseName = uc.Course.CourseName
                    }).ToList(),
        Lecturers = user.UsersLecturers
                        .Where(ul => ul.Lecturer != null)
                        .Select(ul => new LecturerOnlyDto
                        {
                            Id = ul.Lecturer.Id,
                            Name = ul.Lecturer.Name,
                            SurName = ul.Lecturer.SurName,
                            Age = ul.Lecturer.Age
                        }).ToList()
    })
    .AsSplitQuery()
    .ToList();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search