c# - 具有外键的实体框架运行速度极慢

Question

遇到一个实体框架代码，该代码使用数据库中的外键创建带有子对象的对象。生成的 SQL 查询是即时的，但 EF 代码永远运行......

1.检查 EF 的 FK 是否与 DB 的 FK 匹配 2.检查 EF 和 DB 的数据类型是否匹配 3.检查 FK 是否有索引

EF 代码

DAL.NachshonDBDataContext oDB = Config.GetDB(); 
var oQuery =
          from oDALMentor in oDB.Mentors 
          orderby oDALMentor.User.sDisplayName
          select new
          {
              oDALMentor,
              oDALUser = oDALMentor.User,
              oDALSector = oDALMentor.LU_Sector,
              oDALAcademicInstitute = oDALMentor.LU_AcademicInstitute,
              oDALMentoringFramework = oDALMentor.LU_MentoringFramework,
              oDALMentoringCoordinator = oDALMentor.LU_MentoringCoordinator,
              oDALMentorQuestionnaires = oDALMentor.MentorQuestionnaires,
              oDALMentorLangs = oDALMentor.MentorLangs,
};
var result = oQuery.AsParallel().ToList(); // takes very long time

生成的 SQL

SELECT * -- asterisk instead of all the generated fields
    (
    SELECT COUNT(*)
    FROM dbo.MentorQuestionnaires AS t7
    WHERE t7.gUserID = t0.gUserID
    ) AS value
FROM dbo.Mentors AS t0
INNER JOIN dbo.Users AS t1 ON t1.gUserID = t0.gUserID
INNER JOIN dbo.LU_Sectors AS t2 ON t2.nSectorID = t0.nSectorID
INNER JOIN dbo.LU_AcademicInstitutes AS t3 ON t3.nAcademicInstitueID = 
t0.nAcademicInstituteID
INNER JOIN dbo.LU_MentoringFrameworks AS t4 ON t4.nMentoringFrameworkID = 
t0.nMentoringFrameworkID
INNER JOIN dbo.LU_MentoringCoordinators AS t5 ON t5.nMentorCoordinatorID 
= t0.nMentorCoordinatorID
LEFT OUTER JOIN dbo.MentorQuestionnaires AS t6 ON t6.gUserID = t0.gUserID
WHERE t1.nStatus = 1

查询运行速度非常快，检索到 1521 行

标签： c#linqentity-framework-4sqlperformance