Entity Framework是否正式启动SQL命令？ [英] When exactly does Entity Framework Fire a SQL Command?

问题描述

假设我有如下代码：

  public void TestWhenSqlFires（）
 {
 var db =新的Db（）; // EF DbContext 
 var items = db.SimpleObjects; // SimpleObject实现接口IId 
 var byId = WhereId（items，1）; 
 var array = byId.ToArray（）; 
} 
 
 protected IEnumerable< IId> WhereId（IEnumerable< IId> items，int id）
 {
 return items.Where（i => i.Id == id）; 
} 
  

TestWhenSqlFires（）中什么样的行实际上将针对数据库运行？

（这是一个问题，从这个答案）

解决方案

一种查找和测试自己的方法：

打开SQL Server Management Studio，打开一个新的查询，选择数据库EF将运行并运行此查询：

  SELECT top 10 deqs.last_execution_time AS [Time]，dest.TEXT AS [Query] 
 FROM sys.dm_exec_query_stats AS deqs 
 CROSS APPLY sys.dm_exec_sql_text（deqs.sql_handle）AS dest 
 ORDER BY deqs.last_execution_time DESC 
  

这将告诉您对数据库运行的过去10个查询。 p>

在TestWhenSqlFires（）的第一行设置断点，运行代码，然后在遍历每行后运行上述查询。你会发现：

  // C＃Line 1 
 var db = new Db（）; 
 
 --SQL行1 
 SELECT TABLE_SCHEMA SchemaName，TABLE_NAME名称FROM INFORMATION_SCHEMA.TABLES 
 WHERE TABLE_TYPE ='BASE TABLE'
 
 // C＃行2 
 var items = db.SimpleObjects; 
 
 --SQL行2 
 SELECT COUNT（*）FROM [sys]。[databases] WHERE [name] = @ 1 
 
 SELECT [GroupBy1]。 [A1] AS [C1] FROM（
 SELECT COUNT（1）AS [A1] FROM [dbo]。[__ MigrationHistory] ​​AS [Extent1] 
）AS [GroupBy1] 
 
（@ 1 nvarchar（4000））SELECT TOP（1）[Project1]。[C1] AS [C1]，
 [Project1]。[MigrationId] AS [MigrationId]，
 [Project1]。 [Model] AS [Model] FROM（
 SELECT [Extent1]。[MigrationId] AS [MigrationId]，
 [Extent1]。[Model] AS [Model]，1 AS [C1] 
 FROM [dbo]。[__ MigrationHistory] ​​AS [Extent1] 
）AS [Project1] ORDER BY [Project1]。[MigrationId] DESC 
 
 // C＃Line 3 
 var byId = WhereId（items，1）; 
 
 --SQL行3 
 
 // C＃行4 
 var array = byId.ToArray（）; 
 
 --SQL行4 
 SELECT [Extent1]。[Id] AS [Id]，[Extent1]。[Stuff] AS [Stuff] 
 FROM [dbo]。 [SimpleObject] AS [Extent1] 
  

最终的SQL查询是EF实际获取数据。先前的查询只是它的热身份 - 验证数据库是否存在，它使用的是EF5迁移历史记录，并且与当前的迁移历史记录哈希匹配。

所以答案是 - 调用 .ToArray（）之后的第4行（或枚举集合的任何调用，如.ToList（），foreach等）。值得注意的是将其传递给接受IEnumerable的方法，即使有涉及到特定的泛型，也不会枚举集合，所以不要在必要的时候触发SQL。

Suppose I have code like:

public void TestWhenSqlFires()
{
    var db = new Db(); // EF DbContext
    var items = db.SimpleObjects; // SimpleObject implements interface IId
    var byId = WhereId(items, 1);
    var array = byId.ToArray();
}

protected IEnumerable<IId> WhereId(IEnumerable<IId> items, int id)
{
    return items.Where(i => i.Id == id);
}

At what line in TestWhenSqlFires() will SQL actually be run against the database?

(This is a question that spun off from comments on this answer)

解决方案

One way to find out and test for yourself:

Open SQL Server Management Studio, open a new query, select the database EF will be running against and run this query:

SELECT top 10 deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

This tells you the past 10 queries that have run against the database.

Set a breakpoint on the first line of TestWhenSqlFires(), run your code, then run the above query after stepping over each line. You'll find:

// C# Line 1
var db = new Db();

--SQL Line 1
SELECT TABLE_SCHEMA SchemaName, TABLE_NAME Name FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'

// C# Line 2
var items = db.SimpleObjects;

--SQL Line 2
SELECT COUNT(*) FROM [sys].[databases] WHERE [name]=@1

SELECT [GroupBy1].[A1] AS [C1] FROM (
    SELECT COUNT(1) AS [A1] FROM [dbo].[__MigrationHistory] AS [Extent1]
) AS [GroupBy1]

(@1 nvarchar(4000))SELECT TOP (1) [Project1].[C1] AS [C1],
    [Project1].[MigrationId] AS [MigrationId],
    [Project1].[Model] AS [Model]  FROM (
        SELECT [Extent1].[MigrationId] AS [MigrationId],
            [Extent1].[Model] AS [Model], 1 AS [C1]
            FROM [dbo].[__MigrationHistory] AS [Extent1]
     )  AS [Project1]  ORDER BY [Project1].[MigrationId] DESC

// C# Line 3
var byId = WhereId(items, 1);

--SQL Line 3

// C# Line 4
var array = byId.ToArray();

--SQL Line 4
SELECT [Extent1].[Id] AS [Id], [Extent1].[Stuff] AS [Stuff]
    FROM [dbo].[SimpleObject] AS [Extent1]

The final SQL query is EF actually fetching the data. The prior queries are just it warming up - verifying the database exists, that it's using EF5 Migration History, and that it matches the current Migration History hash.

So the answer is - the 4th line, after .ToArray() is called (or any call that enumerates the collection, like .ToList(), foreach, etc). Notably passing it to a method that accepts IEnumerable, even if there's a specific Generic involved, does not enumerate the collection, and so does not fire off SQL any earlier than necessary.

这篇关于Entity Framework是否正式启动SQL命令？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！