我有一个非常慢的EF查询。我如何调试它?

I have the following classes that are backed up by EF6 and the SQL Server 2012 database. I have about 500 records in the question table, 250 in the problem table, and 20 in the subtopic table:

下面的类由EF6和SQL Server 2012数据库支持。我在问题表中有大约500条记录，在问题表中有250条记录，在子主题表中有20条记录:

public class Question
{
    public int QuestionId { get; set; }
    public int ProblemId { get; set; }
    public int QuestionStatusId { get; set; }
    public string Text { get; set; }
    public string AssignedTo { get; set; }
    public DateTime AssignedDate { get; set; }
    public int QuestionTypeId { get; set; }
    public virtual Problem Problem { get; set; }
    public virtual QuestionStatus QuestionStatus { get; set; }
    public virtual QuestionType QuestionType { get; set; }
}
public class Problem
{
    public Problem()
    {
        this.Questions = new List<Question>();
    }
    public int ProblemId { get; set; }
    public int SubTopicId { get; set; }
    public string Text { get; set; }
    public virtual SubTopic SubTopic { get; set; }
    public virtual ICollection<Question> Questions { get; set; }
}
public partial class SubTopic
{
    public SubTopic()
    {;
        this.Problems = new List<Problem>();
    }
    public int SubTopicId { get; set; }
    public int Number { get; set; }
    public int TopicId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Problem> Problems { get; set; }
    public virtual Topic Topic { get; set; }
}

I set up the following maps:

我设置了以下地图:

    public ProblemMap()
    {
        // Primary Key
        this.HasKey(t => t.ProblemId);
        this.HasRequired(t => t.SubTopic)
            .WithMany(t => t.Problems)
            .HasForeignKey(d => d.SubTopicId)
            .WillCascadeOnDelete(false);
    }
    public QuestionMap()
    {
        // Primary Key
        this.HasKey(t => t.QuestionId);
        // Relationships
        this.HasRequired(t => t.Problem)
            .WithMany(t => t.Questions)
            .HasForeignKey(d => d.ProblemId);
        this.HasRequired(t => t.QuestionStatus)
            .WithMany(t => t.Questions)
            .HasForeignKey(d => d.QuestionStatusId);
        this.HasRequired(t => t.QuestionType)
            .WithMany(t => t.Questions)
            .HasForeignKey(d => d.QuestionTypeId);
    }
    public SubTopicMap()
    {
        // Primary Key
        this.HasKey(t => t.SubTopicId);
        // Relationships
        this.HasRequired(t => t.Topic)
            .WithMany(t => t.SubTopics)
            .HasForeignKey(d => d.TopicId);
    }

I am trying to do a query on these that looks like this:

我试图对这些看起来像这样的问题进行查询:

IList<Question> questions;
questions = _questionsRepository
    .GetAll()
    .Where(q => q.Problem.SubTopic.TopicId == topicId || topicId == 0)
    .Where(q => q.QuestionStatusId == questionStatusId || questionStatusId == 0)
    .Where(q => q.AssignedTo == assignedTo || assignedTo == "0")
    .Where(q => q.ModifiedBy == modifiedBy || modifiedBy == "0")
    .Include(q => q.Problem)
    .Include(q => q.Answers)
    .ToList();

The problem is that the query starts but never seems to return. Is there something I am doing wrong? Is there a way that I can find out what kind of query it is trying to execute against the database?

问题是查询开始了，但似乎从未返回。我做错什么了吗?是否有一种方法可以让我知道它试图对数据库执行哪种查询?

Could it be the following that is making things run slow:

可能是以下的原因导致事情进展缓慢:

    q.Problem.SubTopic.TopicId == topicId

Here is the repository GetAll()

这是存储库GetAll()

    public virtual IQueryable<T> GetAll()
    {
        return DbSet;
    }

2 个解决方案

#1

You can use Linq to Entity query visualizer

您可以使用Linq到实体查询可视化工具。

It's a free extension for Visual Studio. Description:

这是Visual Studio的免费扩展。描述:

View Native SQL (MS SQL, DB2, Oracle, etc) of LInQ to entities ObjectQuery while debugging in Visual Studio 2008/2010/. Also view Lambda expression, edit Query parameters, view DB Connection info, view query results, and export results to MS Excel(No MS Excel required)

在Visual Studio 2008/2010/中调试时，查看LInQ的原生SQL (MS SQL、DB2、Oracle等)到实体ObjectQuery。还可以查看Lambda表达式，编辑查询参数，查看DB连接信息，查看查询结果，并将结果导出到MS Excel(不需要MS Excel)

You can then copy the query and paste it in SQL Server if you need to.

然后，如果需要，可以复制查询并将其粘贴到SQL Server中。

You can also use LinqPad. This application lets you write an test LINQ expressions targeted to different database backends and different flavors of LINQ: LINQ to SQL, LINQ to Entities, LINQ to Objects...

你也可以使用LinqPad。这个应用程序允许您编写针对不同数据库后端和不同类型的LINQ的测试LINQ表达式:LINQ to SQL, LINQ to实体，LINQ to Objects…

And finally, the free version of DevArt LINQ Insight (express versions). It's also a VS extension.

最后，DevArt LINQ Insight的免费版本(express版本)。它也是一个VS扩展。

#2

I don't think this query works at all. You do not have a joins between question and problem as well as problem and subtopic. If you rewrite the query with joins, I see no reason for it to not perform properly.

我认为这个查询根本不起作用。问题和问题、问题和子主题之间没有连接。如果您用join重写查询，我认为它没有理由不能正常执行。

Try something like this:

试试这样:

from q in db.Questions
join p in db.Problems on q.ProblemId equals p.ProblemId
join s in db.SubTopics on s.SubTopicId equals p.SubTopicId
where q.QuestionStatusId == questionStatusId &&
q.AssignedTo == assignedTo
q.ModifiedBy == modifiedBy
s.TopicId == topicId
select q

In order to debug I use SQL Profiler. Once you have the query from SQL Profiler, you can use the Database Engine Tuning Advisor to see if you are missing indexes on your DB.

为了调试，我使用SQL分析器。一旦有了来自SQL分析器的查询，就可以使用数据库引擎优化顾问来查看数据库上是否缺少索引。

#1