c# - 该查询已被取消，因为此查询的估计成本 (27195) 超过了 SQL Server 2014 中配置的阈值 3000

Question

我正在使用 LINQ 查询来获取加入多个表的行，还使用 ​​let 关键字来获取行列表以在显示列中显示多个值。这个 linq 查询有什么问题？

图像链接

(from app in _dbContext.Appointments
 join patient in _dbContext.Patients on app.PatientID equals patient.ID
 join doc in _dbContext.Doctors on app.DoctorID equals doc.ID
 join avail in _dbContext.DoctorsAvailabilities on app.AvailabilityID equals avail.ID
 join loc in _dbContext.OfficeLocations on app.LocationID equals loc.ID
 join state in _dbContext.CategoryDetails on loc.USState equals state.ItemID into StateGroup
 from state in StateGroup.DefaultIfEmpty()
 join appoint in _dbContext.CategoryDetails on app.Status equals appoint.ItemID into AppointmentStatusGroup
 from appoint in AppointmentStatusGroup.DefaultIfEmpty()

 let reasonVisit = (from v in _dbContext.DoctorAppointmentVisitReasons
                    join c in _dbContext.CategoryDetails on v.ReasonID equals c.ItemID
                    where c.CategoryID == CatergoryConstant.ReasonofVisit && v.AppointmentID == app.ID
                    select c.MasterData).ToList()
 join appointmentSource in _dbContext.CategoryDetails on app.AppointmentSource equals appointmentSource.ItemID into AppointmentSourceGroup
 from appointmentSource in AppointmentSourceGroup.DefaultIfEmpty()
 let getReport = (from st in _dbContext.DoctorReportStatus
                  join cat in _dbContext.CategoryDetails on st.ReportStatus equals cat.ItemID
                  where st.StatusID == StatusConstant.Active && st.AppointmentID == app.ID
                  select new DoctorReportStatusViewModel() { ReportStatusText = cat.MasterData, ReportStatus = st.ReportStatus, AppointmentID = st.AppointmentID }).ToList()

 let specility = (from s in _dbContext.DoctorSpecialities
                  join c in _dbContext.CategoryDetails on s.SpecialitiesID equals c.ItemID
                  where c.CategoryID == CatergoryConstant.DoctorSpeciality && s.DoctorID == doc.ID
                  select c.MasterData).ToList()

标签： c#sql-serverasp.net-mvcperformancelinq-to-entities