I have a table named “Applications” where I keep track of the applications made by my candidates to vacancies. Now, I need to write a statistical query for HRs, and when an HR logs in, I should bring the candidates who applied to the vacancies created by that HR. Since there are filters concurrently in my query, I had to include other tables related to candidates as well. However, my query is slow and consumes an incredible amount of RAM. My 16 GB RAM Linux server gets filled up after 1-2 hours of running the project and crashes it. When I checked the server, I saw that this query alone was constantly eating up RAM. How do you think I can optimize this, or what might be wrong with the query? Additionally, there are +400,000 applications and +100,000 candidates
var entities = _context.Applications.Include(p => p.Candidate)
.ThenInclude(p => p.CandidateIndustryFields)
.ThenInclude(p => p.DesiredField)
.Include(p => p.Vacancy)
.Include(p => p.Candidate)
.ThenInclude(p => p.CandidateIndustrySubFields)
.ThenInclude(p => p.SubField)
.Include(p => p.Candidate)
.ThenInclude(x => x.CandidateSkills)
.ThenInclude(x => x.Skill)
.Include(p => p.Candidate)
.ThenInclude(x => x.CandidateBackgrounds)
.ThenInclude(x => x.WorkExperienceType)
.Where(m => (m.Vacancy.RecruiterId == userId) && (m.StatusId == searchParameter.StatusId || searchParameter.StatusId == null)
&& (string.IsNullOrEmpty(searchParameter.Name) || m.Candidate.Name.Contains(searchParameter.Name))
&& (string.IsNullOrEmpty(searchParameter.Surname) || m.Candidate.Surname.Contains(searchParameter.Surname))
&& (string.IsNullOrEmpty(searchParameter.PassportFin) || m.Candidate.CandidatePassports.Any(p => p.PassportFin == searchParameter.PassportFin.ToUpper()))
&& (string.IsNullOrEmpty(searchParameter.Patronymic) || m.Candidate.FatherName.Contains(searchParameter.Patronymic))
&& (searchParameter.Gender == null || m.Candidate.GenderId == searchParameter.Gender)
&& (searchParameter.MaritalStatus == null || m.Candidate.MaritalStatusId == searchParameter.MaritalStatus)
&& (searchParameter.MilitaryStatus == null || m.Candidate.MillitaryStatusId == searchParameter.MilitaryStatus)
&& (searchParameter.HasPhoto == null || (searchParameter.HasPhoto == 0 ? m.Candidate.CandidateImage == null : m.Candidate.CandidateImage != null))
&& (searchParameter.MinBirthDate == null || m.Candidate.BirthDate >= searchParameter.MinBirthDate)
&& (searchParameter.MaxBirthDate == null || m.Candidate.BirthDate <= searchParameter.MaxBirthDate)
&& (string.IsNullOrEmpty(searchParameter.Address) || m.Candidate.CandidateContactInfo.ActualAddress.Contains(searchParameter.Address) || m.Candidate.CandidateContactInfo.RegisteredAddress.Contains(searchParameter.Address))
&& (string.IsNullOrEmpty(searchParameter.Email) || m.Candidate.User.Email.Contains(searchParameter.Email))
&& (regions.Count == 0 || regions.Contains(m.Candidate.CandidateContactInfo.RegionId.Value))
&& (districts.Count == 0 || districts.Contains(m.Candidate.CandidateContactInfo.DistrictId.Value))
&& (educationDegrees.Count == 0 || m.Candidate.CandidateEducations.Any(k => educationDegrees.Contains(k.DegreeId)) || m.Candidate.CandidateCustomEducations.Any(l => educationDegrees.Contains(l.DegreeId)))
&& (searchParameter.EntranceScoreMin == null || m.Candidate.CandidateEducations.Any(k => k.EntrancePoint >= searchParameter.EntranceScoreMin) || m.Candidate.CandidateCustomEducations.Any(l => l.EntrancePoint >= searchParameter.EntranceScoreMin))
&& (searchParameter.EntranceScoreMax == null || m.Candidate.CandidateEducations.Any(k => k.EntrancePoint <= searchParameter.EntranceScoreMax) || m.Candidate.CandidateCustomEducations.Any(l => l.EntrancePoint <= searchParameter.EntranceScoreMax))
&& (string.IsNullOrEmpty(searchParameter.Profession) || m.Candidate.CandidateCustomEducations.Any(p => p.Profession.Contains(searchParameter.Profession)) || m.Candidate.CandidateEducations.Any(p => p.Profession.Contains(searchParameter.Profession)))
&& (string.IsNullOrEmpty(searchParameter.Position) || m.Candidate.CandidateBackgrounds.Any(k => k.Position.Contains(searchParameter.Position)))
&& (string.IsNullOrEmpty(searchParameter.CompanyName) || m.Candidate.CandidateBackgrounds.Any(k => k.CompanyName.Contains(searchParameter.CompanyName)))
&& (computerSkills.Count == 0 || m.Candidate.ComputerSkills.Any(l => computerSkills.Contains(l.SkillId)))
//&& (languageSkills.Count == 0 || m.Candidate.CandidateLanguages.Any(l => languageSkills.Any(c => c.LanguageId == l.LanguageId && c.Writing >= l.Writing && c.Reading >= l.Reading && c.Speaking >= l.Speaking)))
&& (string.IsNullOrEmpty(searchParameter.Phone) || m.Candidate.CandidateMobilPhones.Any(l => l.PhoneNumber.Contains(searchParameter.Phone)))
&& (string.IsNullOrEmpty(searchParameter.CertificateName) || m.Candidate.CandidateCertificates.Any(n => n.Name.Contains(searchParameter.CertificateName)))
&& (string.IsNullOrEmpty(searchParameter.Organization) || m.Candidate.CandidateCertificates.Any(n => n.IssuedBy.Contains(searchParameter.Organization)))
&& (fields.Count == 0 || m.Candidate.CandidateIndustryFields.Any(l => fields.Contains(l.DesiredFieldId)))
&& (skills.Count == 0 || m.Candidate.CandidateSkills.Any(l => skills.Contains(l.SkillId)))
&& (workExperiences.Count == 0 || m.Candidate.CandidateBackgrounds.Any(l => workExperiences.Contains(l.WorkExperienceId ?? -1)))
&& (searchParameter.HasExperience == null || (!searchParameter.HasExperience.Value ? m.Candidate.CandidateBackgrounds.Count == 0 : m.Candidate.CandidateBackgrounds.Count != 0))
&& (searchParameter.GeneralSearch == null || m.Candidate.Name.Contains(searchParameter.GeneralSearch)
|| m.Candidate.Surname.Contains(searchParameter.GeneralSearch)
|| m.Candidate.FatherName.Contains(searchParameter.GeneralSearch)
|| m.Candidate.CandidateContactInfo.ActualAddress.Contains(searchParameter.GeneralSearch)
|| m.Candidate.CandidateContactInfo.RegisteredAddress.Contains(searchParameter.GeneralSearch)
|| m.Candidate.User.Email.Contains(searchParameter.GeneralSearch)
|| m.Candidate.CandidateCustomEducations.Any(p => p.InstitutionName.Contains(searchParameter.GeneralSearch))
|| m.Candidate.CandidateCustomEducations.Any(p => p.Profession.Contains(searchParameter.GeneralSearch))
|| m.Candidate.CandidateEducations.Any(p => p.Profession.Contains(searchParameter.GeneralSearch))
|| m.Candidate.CandidateBackgrounds.Any(p => p.Position.Contains(searchParameter.GeneralSearch))
|| m.Candidate.CandidateBackgrounds.Any(p => p.CompanyName.Contains(searchParameter.GeneralSearch))
|| m.Candidate.CandidateMobilPhones.Any(p => p.PhoneNumber.Contains(searchParameter.GeneralSearch))
|| m.Candidate.CandidateCertificates.Any(p => p.Name.Contains(searchParameter.GeneralSearch))
|| m.Candidate.CandidateCertificates.Any(p => p.IssuedBy.Contains(searchParameter.GeneralSearch))));