实体框架多次调用SQL Server

I have a simple Web MVC app using database first Entity Framework. I am retrieving information from a table. It is very slow.

我有一个简单的Web MVC应用程序，使用的是数据库第一实体框架。我正在从一个表中检索信息。这是非常缓慢。

I have two problems:

我有两个问题:

When I review the query with SQL Server Profiler, it shows the query as a Select query with a from clause that is another select query. I'm not sure why it is using another select instead of the table name. Could this be slowing down the query?

当我使用SQL Server Profiler检查查询时，它将查询显示为带有from子句(另一个Select查询)的Select查询。我不确定为什么它使用另一个select而不是表名。这会减慢查询吗?
When I am retrieving the rows, it is calling the database for each row slowing down the process. It is using the key from the table and requerying the database for each row. Why is it recalling the database for each row? Didn't the first query return all the rows?

当我检索行时，它正在为每一行调用数据库，从而减慢进程。它使用表中的键并为每一行请求数据库。为什么要为每一行调用数据库?第一个查询不是返回了所有的行吗?

Below is my code and SQL Server Profiler results.

下面是我的代码和SQL Server分析器结果。

Code:

代码:

List<FanDetail> fans = db.FanDetails.ToList();

foreach (var item in fans)
{
   FanSummaryViewModel add = new FanSummaryViewModel()
   {
     Part_No = item.Part_No,
   }
}

SQL Server Profiler results for the query:

SQL Server Profiler查询结果:

SELECT 
    [Extent1].[Part_No] AS [Part_No]
FROM 
    (SELECT 
         [FanDetails].[Part_No] AS [Part_No]
     FROM 
         [dbo].[FanDetails] AS [FanDetails]) AS [Extent1]

SQL Profiler Results on the For Each:

SQL Profiler的结果是:

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[Part_No] AS [Part_No]
    FROM (SELECT 
    [FanDetails].[Part_No] AS [Part_No]
    FROM [dbo].[FanDetails] AS [FanDetails]) AS [Extent1]
    WHERE [Extent1].[Part_No] = = @p__linq__0',N'@p__linq__0 varchar(8000)',@p__linq__0='00405635'

Let me know if I need to provide more detail.

如果我需要提供更多的细节，请告诉我。

I'm new to EF and been working on this performance issue for a while.

我是英孚的新成员，在这个性能问题上工作了一段时间。

1 个解决方案

#1

You should be able to achieve all the query and projection in a single query:

您应该能够在一个查询中实现所有查询和投影:

List<FanSummaryViewModel> fans = db.FanDetails
                                  .Select(item => new FanSummaryViewModel 
                                               {
                                                  Part_No = item.Part_No
                                               })
                                  .ToList();

The Select statement before the ToList will help the parser create a properly scoped query and will only return the fields referred to and create a new object for each row when the list is enumerated, which is what the ToList statement achieves.

ToList之前的Select语句将帮助解析器创建一个适当范围的查询，并且只返回引用的字段，并在枚举列表时为每一行创建一个新对象，这是ToList语句实现的。

You should also check the query performance of the generated Sql against the database directly to see if the issue is with the database only, which you may be able to fix with an index.

您还应该直接检查生成的Sql对数据库的查询性能，以查看问题是否仅与数据库有关，您可以用索引来修复。

#1