I had a query which was taking too long to return the results but I tried to change it and group the results on the basis of ID but it is now returning more results than before it may be due to duplicated results are returned.
Here is the old Query:
(from p in se.CompanyRepresentatives
let internshipApplication = (from q in p.QuestionnaireResponses
where q.QuestionnaireID == SurveyEntities.STUDENT_INTERNSHIP_PROFILE
select q).FirstOrDefault()
let pendingApplications = (from q in p.InternshipMatches
where InterneeViewModel.PENDING_INTRO_MATCH_STATUSES.Contains(q.Status)
select q).Count()
let rejectedApplications = (from q in p.InternshipMatches
where InterneeViewModel.REJECTED_INTRO_STATUSES.Contains(q.Status) &&
//Only current year's internships
DateTime.Compare(DbFunctions.TruncateTime(q.Internship.Created).Value, yearStart) > 0 &&
DateTime.Compare(DbFunctions.TruncateTime(q.Internship.Created).Value, yearEnd) < 0
select q).Count()
where
//Only current year's internship applications
DateTime.Compare(DbFunctions.TruncateTime(internshipApplication.Updated).Value, yearStart) > 0 &&
DateTime.Compare(DbFunctions.TruncateTime(internshipApplication.Updated).Value, yearEnd) < 0 &&
interneeIds.Contains(p.ID) && internshipApplication != null
select new
{
ID = p.ID,
Name = p.FirstName + " " + p.LastName,
Email = p.Email,
University = p.TamidChapter.Name,
MatchStatus = p.RespondentStatus,
Rank = p.Rank,
Posted = internshipApplication.Updated,
Visible = !p.IsHidden,
lang = p.Languages,
grad = p.Graduation,
major = p.Majors,
pendingApplications,
rejectedApplications,
industry_1 = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRY_1).Answer,
industry_2 = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRY_2).Answer,
industry_3 = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRY_3).Answer,
industryOther = p.Responses.Where(y => y.QuestionID == INDUSTRY_OTHER).Select(x=>x.Answer),
industryEss = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRIES_REASON).Answer,
preferredCompanyType = p.Responses.Where(y => y.QuestionID == COMPANY_TYPE).Select(x => x.Answer),
jobDes_1 = p.Responses.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_1).Answer,
jobDes_2 = p.Responses.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_2).Answer,
jobDes_3 = p.Responses.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_3).Answer,
jobDesOther = p.Responses.Where(y => y.QuestionID == JOB_DESCRIPTION_OTHER).Select(x => x.Answer),
market = p.Responses.Where(y => y.QuestionID == INTERESTED_MARKETS).Select(x => x.Answer),
marketReason = p.Responses.FirstOrDefault(x => x.QuestionID == INTERESTED_MARKETS_REASON).Answer,
codelang = p.Responses.Where(y => y.QuestionID == CODING_LANGUAGES).Select(x => x.Answer),
CONSULTING_FINANCE_VC = p.Responses.Where(y => y.QuestionID == CONSULTING_FINANCE_VC).Select(x => x.Answer),
officeSize = p.Responses.FirstOrDefault(x => x.QuestionID == OFFICE_SIZE).Answer,
preferredType = p.Responses.Where(y => y.QuestionID == EXPECTED_EXPERIENCE).Select(x => x.Answer),
hourComute = p.Responses.FirstOrDefault(x => x.QuestionID == WILL_COMMUTE).Answer,
cityPlacement = p.Responses.FirstOrDefault(x => x.QuestionID == LOCATION).Answer,
workHours = p.Responses.FirstOrDefault(x => x.QuestionID == WORK_DAY).Answer,
specCompany = p.Responses.FirstOrDefault(x => x.QuestionID == SPEC_COMPANY).Answer,
consultingCompany = p.Responses.FirstOrDefault(x => x.QuestionID == INTERNSHIP_WITH_CONSULTING_COMPANY).AnswerCode
}).ToList().Select(p =>
new InterneeViewModel
{
ID = p.ID,
Name = p.Name,
Email = p.Email,
University = p.University,
Status = (IndividualStatus)Enum.Parse(typeof(IndividualStatus), p.MatchStatus),
Rank = p.Rank,
Posted = p.Posted,
Visible = p.Visible,
Languages = p.lang,
Graduation = p.grad.ToString(),
Majors = p.major,
PendingMatches = p.pendingApplications,
NoOfRejectedApplication = p.rejectedApplications,
Industry1 = p.industry_1,
Industry2 = p.industry_2,
Industry3 = p.industry_3,
IndustryOther = p.industryOther.ToList(),
IndustriesReason = p.industryEss,
CompanyTypes = p.preferredCompanyType.ToList(),
JobDesc1 = p.jobDes_1,
JobDesc2 = p.jobDes_2,
JobDesc3 = p.jobDes_3,
JobDescOther = p.jobDesOther.ToList(),
InterestedMarkets = p.market.ToList(),
InterestedMarketsReason = p.marketReason,
CodingLanguages = p.codelang.ToList(),
ConsultingFinanceVC = p.CONSULTING_FINANCE_VC.ToList(),
OfficeSize = p.officeSize,
TypesOfExperience = p.preferredType.ToList(),
IsCommuter = p.hourComute,
Location = p.cityPlacement,
WorkDay = p.workHours,
SpecCompany = p.specCompany,
InternshipWithConsultingCompany = "Yes".Equals(p.consultingCompany, StringComparison.CurrentCultureIgnoreCase)
}).ToList();
As you may see it is querying repeatedly for getting different results in select statement. I grouped the responses on the basis of questionID then I query that grouped list for different results but it is returning more results than before it looks like I am doing something wrong.
Here is the new Query grouped Responses by QuestionID:
(from p in se.CompanyRepresentatives
from questionRes in p.Responses.GroupBy(x => x.QuestionID)
let internshipApplication = (from q in p.QuestionnaireResponses
where q.QuestionnaireID == SurveyEntities.STUDENT_INTERNSHIP_PROFILE
select q).FirstOrDefault()
let pendingApplications = (from q in p.InternshipMatches
where InterneeViewModel.PENDING_INTRO_MATCH_STATUSES.Contains(q.Status)
select q).Count()
let rejectedApplications = (from q in p.InternshipMatches
where InterneeViewModel.REJECTED_INTRO_STATUSES.Contains(q.Status) &&
//Only current year's internships
DateTime.Compare(DbFunctions.TruncateTime(q.Internship.Created).Value, yearStart) > 0 &&
DateTime.Compare(DbFunctions.TruncateTime(q.Internship.Created).Value, yearEnd) < 0
select q).Count()
where
//Only current year's internship applications
DateTime.Compare(DbFunctions.TruncateTime(internshipApplication.Updated).Value, yearStart) > 0 &&
DateTime.Compare(DbFunctions.TruncateTime(internshipApplication.Updated).Value, yearEnd) < 0 &&
interneeIds.Contains(p.ID) && internshipApplication != null
select new
{
ID = p.ID,
Name = p.FirstName + " " + p.LastName,
Email = p.Email,
University = p.TamidChapter.Name,
MatchStatus = p.RespondentStatus,
Rank = p.Rank,
Posted = internshipApplication.Updated,
Visible = !p.IsHidden,
lang = p.Languages,
grad = p.Graduation,
major = p.Majors,
pendingApplications,
rejectedApplications,
industry_1 = questionRes.FirstOrDefault(x => x.QuestionID == INDUSTRY_1).Answer,
industry_2 = questionRes.FirstOrDefault(x => x.QuestionID == INDUSTRY_2).Answer,
industry_3 = questionRes.FirstOrDefault(x => x.QuestionID == INDUSTRY_3).Answer,
industryOther = questionRes.Where(y => y.QuestionID == INDUSTRY_OTHER).Select(x=>x.Answer),
industryEss = questionRes.FirstOrDefault(x => x.QuestionID == INDUSTRIES_REASON).Answer,
preferredCompanyType = questionRes.Where(y => y.QuestionID == COMPANY_TYPE).Select(x => x.Answer),
jobDes_1 = questionRes.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_1).Answer,
jobDes_2 = questionRes.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_2).Answer,
jobDes_3 = questionRes.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_3).Answer,
jobDesOther = questionRes.Where(y => y.QuestionID == JOB_DESCRIPTION_OTHER).Select(x => x.Answer),
market = questionRes.Where(y => y.QuestionID == INTERESTED_MARKETS).Select(x => x.Answer),
marketReason = questionRes.FirstOrDefault(x => x.QuestionID == INTERESTED_MARKETS_REASON).Answer,
codelang = questionRes.Where(y => y.QuestionID == CODING_LANGUAGES).Select(x => x.Answer),
CONSULTING_FINANCE_VC = questionRes.Where(y => y.QuestionID == CONSULTING_FINANCE_VC).Select(x => x.Answer),
officeSize = questionRes.FirstOrDefault(x => x.QuestionID == OFFICE_SIZE).Answer,
preferredType = questionRes.Where(y => y.QuestionID == EXPECTED_EXPERIENCE).Select(x => x.Answer),
hourComute = questionRes.FirstOrDefault(x => x.QuestionID == WILL_COMMUTE).Answer,
cityPlacement = questionRes.FirstOrDefault(x => x.QuestionID == LOCATION).Answer,
workHours = questionRes.FirstOrDefault(x => x.QuestionID == WORK_DAY).Answer,
specCompany = questionRes.FirstOrDefault(x => x.QuestionID == SPEC_COMPANY).Answer,
consultingCompany = questionRes.FirstOrDefault(x => x.QuestionID == INTERNSHIP_WITH_CONSULTING_COMPANY).AnswerCode
}).ToList().Select(p =>
new InterneeViewModel
{
ID = p.ID,
Name = p.Name,
Email = p.Email,
University = p.University,
Status = (IndividualStatus)Enum.Parse(typeof(IndividualStatus), p.MatchStatus),
Rank = p.Rank,
Posted = p.Posted,
Visible = p.Visible,
Languages = p.lang,
Graduation = p.grad.ToString(),
Majors = p.major,
PendingMatches = p.pendingApplications,
NoOfRejectedApplication = p.rejectedApplications,
Industry1 = p.industry_1,
Industry2 = p.industry_2,
Industry3 = p.industry_3,
IndustryOther = p.industryOther.ToList(),
IndustriesReason = p.industryEss,
CompanyTypes = p.preferredCompanyType.ToList(),
JobDesc1 = p.jobDes_1,
JobDesc2 = p.jobDes_2,
JobDesc3 = p.jobDes_3,
JobDescOther = p.jobDesOther.ToList(),
InterestedMarkets = p.market.ToList(),
InterestedMarketsReason = p.marketReason,
CodingLanguages = p.codelang.ToList(),
ConsultingFinanceVC = p.CONSULTING_FINANCE_VC.ToList(),
OfficeSize = p.officeSize,
TypesOfExperience = p.preferredType.ToList(),
IsCommuter = p.hourComute,
Location = p.cityPlacement,
WorkDay = p.workHours,
SpecCompany = p.specCompany,
InternshipWithConsultingCompany = "Yes".Equals(p.consultingCompany, StringComparison.CurrentCultureIgnoreCase)
}).ToList();
Try the following query. Main idea to load responses and process them on the client side. Also change way how to work with dates, TruncateTime disables indexes, if they exists for sure.
var yearStart = ...
var yearEnd = yearStart.AddYears(1);
var rawQuery =
from p in se.CompanyRepresentatives
from internshipApplication in p.QuestionnaireResponses
.Where(q => q.QuestionnaireID == SurveyEntities.STUDENT_INTERNSHIP_PROFILE)
.Take(1)
let pendingApplications = (from q in p.InternshipMatches
where InterneeViewModel.PENDING_INTRO_MATCH_STATUSES.Contains(q.Status)
select q).Count()
let rejectedApplications = (from q in p.InternshipMatches
where InterneeViewModel.REJECTED_INTRO_STATUSES.Contains(q.Status) &&
//Only current year's internships
q.Internship.Created.Value >= yearStart &&
q.Internship.Created.Value < yearEnd
select q).Count()
where
//Only current year's internship applications
internshipApplication.Updated.Value >= yearStart &&
internshipApplication.Updated.Value < yearEnd &&
interneeIds.Contains(p.ID)
select new
{
ID = p.ID,
Name = p.FirstName + " " + p.LastName,
Email = p.Email,
University = p.TamidChapter.Name,
MatchStatus = p.RespondentStatus,
Rank = p.Rank,
Posted = internshipApplication.Updated,
Visible = !p.IsHidden,
lang = p.Languages,
grad = p.Graduation,
major = p.Majors,
pendingApplications,
rejectedApplications,
Responses = p.Responses.Select(r => new { r.Answer, r.AnswerCode, r.QuestionID }).ToList()
};
var result = rawQuery
.AsEnumerable()
.Select(p => new InterneeViewModel
{
ID = p.ID,
Name = p.Name,
Email = p.Email,
University = p.University,
Status = (IndividualStatus)Enum.Parse(typeof(IndividualStatus), p.MatchStatus),
Rank = p.Rank,
Posted = p.Posted,
Visible = p.Visible,
Languages = p.lang,
Graduation = p.grad.ToString(),
Majors = p.major,
PendingMatches = p.pendingApplications,
NoOfRejectedApplication = p.rejectedApplications,
Industry1 = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRY_1)?.Answer,
Industry2 = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRY_2)?.Answer,
Industry3 = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRY_3)?.Answer,
IndustryOther = p.Responses.Where(y => y.QuestionID == INDUSTRY_OTHER).Select(x=> x.Answer).ToList(),
IndustriesReason = p.Responses.FirstOrDefault(x => x.QuestionID == INDUSTRIES_REASON)?.Answer,
CompanyTypes = p.Responses.Where(y => y.QuestionID == COMPANY_TYPE).Select(x => x.Answer).ToList(),
JobDesc1 = p.Responses.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_1)?.Answer,
JobDesc2 = p.Responses.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_2)?.Answer,
JobDesc3 = p.Responses.FirstOrDefault(x => x.QuestionID == JOB_DESCRIPTION_3)?.Answer,
JobDescOther = p.Responses.Where(y => y.QuestionID == JOB_DESCRIPTION_OTHER).Select(x => x.Answer).ToList(),
InterestedMarkets = p.Responses.Where(y => y.QuestionID == INTERESTED_MARKETS).Select(x => x.Answer).ToList(),
InterestedMarketsReason = p.Responses.FirstOrDefault(x => x.QuestionID == INTERESTED_MARKETS_REASON)?.Answer,
CodingLanguages = p.Responses.Where(y => y.QuestionID == CODING_LANGUAGES).Select(x => x.Answer).ToList(),
ConsultingFinanceVC = p.Responses.Where(y => y.QuestionID == CONSULTING_FINANCE_VC).Select(x => x.Answer).ToList(),
OfficeSize = p.Responses.FirstOrDefault(x => x.QuestionID == OFFICE_SIZE)?.Answer,
TypesOfExperience = p.Responses.Where(y => y.QuestionID == EXPECTED_EXPERIENCE).Select(x => x.Answer).ToList(),
IsCommuter = p.Responses.FirstOrDefault(x => x.QuestionID == WILL_COMMUTE)?.Answer,
Location = p.Responses.FirstOrDefault(x => x.QuestionID == LOCATION)?.Answer,
WorkDay = p.Responses.FirstOrDefault(x => x.QuestionID == WORK_DAY)?.Answer,
SpecCompany = p.Responses.FirstOrDefault(x => x.QuestionID == SPEC_COMPANY)?.Answer,
InternshipWithConsultingCompany = "Yes".Equals(p.Responses.FirstOrDefault(x => x.QuestionID == INTERNSHIP_WITH_CONSULTING_COMPANY)?.AnswerCode, StringComparison.CurrentCultureIgnoreCase)
})
.ToList();