实体框架6查询性能(M关系)

Question

我需要使用EF从多个表中查询多个列。当我在select查询中不包括M关系时，一切都进行得很好，性能也很好。

具有M关系的查询：

result = (from s in db.Member
                      .Include(i => i.Category)
                      .Include(i => i.MemberWorkEntity)
                      .Include(i => i.Status)
                      .Include(i => i.DiscountMethod)
                      .Where(i => i.C_deleted == null)
      select new MemberDTO
      {
          memberNumber = s.memberNumber,
          name = s.name,
          status = s.Status.name,
          email = s.email,
          phone = s.phone,
          mobile = s.mobile,
          fax = s.fax,
          workEntity = (from e in db.WorkEntity.Where(i => i.workEntityLevelID == 2)
                        join sc in s.MemberWorkEntity on e.workEntityID equals sc.workEntityID
                        select e.name).FirstOrDefault(),
          category = s.Category.name,
          discountMethod = s.DiscountMethod.name,
          delegate = s.delegate ? "Yes" : "No",
          leader = s.leader ? "Yes" : "No"

      }).AsNoTracking().ToList<MemberDTO>();

30000记录执行时间(ms)：

|1st Execution: 1376
|2nd Execution: 160
|3rd Execution: 145

没有M关系的查询：

result = (from s in db.Member
                      .Include(i => i.Category)
                      .Include(i => i.MemberWorkEntity)
                      .Include(i => i.Status)
                      .Include(i => i.DiscountMethod)
                      .Where(i => i.C_deleted == null)
      select new MemberDTO
      {
          memberNumber = s.memberNumber,
          name = s.name,
          status = s.Status.name,
          email = s.email,
          phone = s.phone,
          mobile = s.mobile,
          fax = s.fax,
          //removed M-M Relationship Query
          category = s.Category.name,
          discountMethod = s.DiscountMethod.name,
          delegate = s.delegate ? "Yes" : "No",
          leader = s.leader ? "Yes" : "No"

      }).AsNoTracking().ToList<MemberDTO>();

30000记录执行时间(ms)：

|1st Execution: 1286
|2nd Execution: 79
|3rd Execution: 67

为什么会有这样的差别(平均慢2倍)？如何提高查询性能？

更新:成员与WorkEntity之间的关系

​

更新:基于@AndreFilimon的建议：更新了我的查询

IEnumerable<WorkEntity> workEntities = db.WorkEntity.AsNoTracking().Where(i => i.workEntityLevelID == 2);

result = (from s in db.Member
                      .Include(i => i.Category)                          
                      .Include(i => i.Status)
                      .Include(i => i.DiscountMethod)
                      .Where(i => i.C_deleted == null)
      select new MemberDTO
      {
          memberNumber = s.memberNumber,
          name = s.name,
          status = s.Status.name,
          email = s.email,
          phone = s.phone,
          mobile = s.mobile,
          fax = s.fax,
          workEntity = (from e in workEntities
                        join sc in s.MemberWorkEntity on e.workEntityID equals sc.workEntityID
                        select e.name).FirstOrDefault(),
          category = s.Category.name,
          discountMethod = s.DiscountMethod.name,
          delegate = s.delegate ? "Yes" : "No",
          leader = s.leader ? "Yes" : "No"

      }).AsNoTracking().ToList<MemberDTO>();

30000记录执行时间(ms)：

|1st Execution: 1364
|2nd Execution: 122
|3rd Execution: 120

更新:如@agfc建议的那样，向我的成员表添加了一个简单的索引：

IEnumerable<WorkEntity> workEntities = db.WorkEntity.AsNoTracking().Where(i => i.workEntityLevelID == 2);

result = (from s in db.Member
                      .Include(i => i.Category)                          
                      .Include(i => i.Status)
                      .Include(i => i.DiscountMethod)
                      .Where(i => i.C_deleted == null)
      select new MemberDTO
      {
          memberNumber = s.memberNumber,
          name = s.name,
          status = s.Status.name,
          email = s.email,
          phone = s.phone,
          mobile = s.mobile,
          fax = s.fax,
          workEntity = (from e in workEntities
                        join sc in s.MemberWorkEntity on e.workEntityID equals sc.workEntityID
                        select e.name).FirstOrDefault(),
          category = s.Category.name,
          discountMethod = s.DiscountMethod.name,
          delegate = s.delegate ? "Yes" : "No",
          leader = s.leader ? "Yes" : "No"

      }).AsNoTracking().ToList<MemberDTO>();

30000记录执行时间(ms)：

|1st Execution: 1544
|2nd Execution: 109
|3rd Execution: 105

更新:基于@Klinger的回答：更改的查询

result = db.MemberWorkEntity.Where(mw => mw.WorkEntity.workEntityLevelID == 2 && mw.Member.C_deleted == null)
        .Select(s => new MemberDTO 
        {
            memberNumber = mw.Member.memberNumber,
            name = mw.Member.name,
            status = mw.Member.Status.name,
            email = mw.Member.email,
            phone = mw.Member.phone,
            mobile = mw.Member.mobile,
            fax = mw.Member.fax,
            workEntity = mw.WorkEntity.name,
            category = mw.Member.Category.name,
            discountMethod = mw.Member.DiscountMethod.name,
            @delegate = mw.Member.@delegate ? "Yes" : "No",
            leader = mw.Member.leader ? "Yes" : "No"
        }).ToList();

30000记录执行时间(ms)：

|1st Execution: 1427
|2nd Execution: 80
|3rd Execution: 76

Answer 1

在不查看实体的确切形状的情况下，应该这样做：

result = db.MemberWorkEntity.Where(mw => mw.WorkEntity.workEntityLevelID == 2 && mw.Member.C_deleted == null)
        .Select(s => new MemberDTO 
{
  memberNumber = mw.Member.memberNumber,
  name = mw.Member.name,
  status = mw.Member.Status.name,
  email = mw.Member.email,
  phone = mw.Member.phone,
  mobile = mw.Member.mobile,
  fax = mw.Member.fax,
  workEntity = mw.WorkEntity.name,
  category = mw.Member.Category.name,
  discountMethod = mw.Member.DiscountMethod.name,
  @delegate = mw.Member.@delegate ? "Yes" : "No",
  leader = mw.Member.leader ? "Yes" : "No"
}).ToList();

不需要使用Include，因为您正在投影到DTO中，而不是实体中。DTO将不具有您所包含的实体的导航属性。当实体返回时，Include用于紧急加载。

Answer 2

尝试在where子句之后移动includes，您也应该在这里获得一些总体性能:还可以将内部查询移出主查询，以避免连接如下所示：

var workEntities=(from e in db.WorkEntity).Where(e=>e.workEntityLevelID==2).ToList();
var result = (from s in db.Member.Where(i => i.C_deleted == null).Include(i => i.Category).Include(i => i.MemberWorkEntity).Include(i => i.Status).Include(i => i.DiscountMethod)
          select new MemberDTO
          {
              memberNumber = s.memberNumber,
              name = s.name,
              status = s.Status.name,
              email = s.email,
              phone = s.phone,
              mobile = s.mobile,
              fax = s.fax,
              workEntity = workEntities.Where(e=>e.workEntityID ==sc.workEntityID).DefaultIfEmpty().Select(e=>e.name).FirstOrDefault(),
              category = s.Category.name,
              discountMethod = s.DiscountMethod.name,
              delegate = s.delegate ? "Yes" : "No",
              leader = s.leader ? "Yes" : "No"

          }).AsNoTracking().ToList<MemberDTO>();