SQL Server Profiler不能跟踪所有Entity Framework 4的查询 - 问题

Question

我正在阅读《Programming Entity Framework, 2nd Edition》一书，其中第一章节有一个非常简单的查询：

我正在阅读《编程Entity Framework，第二版》，第一章中有一个非常简单的查询：

    private static void QueryContacts()
    {
        using (var context = new SampleEntities())
        {
            var contacts = context.Contacts.Where(c => c.FirstName == "Robert");
            var res = (contacts as ObjectQuery<Contact>).Execute(MergeOption.NoTracking);

            foreach (var contact in res)
            {
                foreach (var address in contact.Addresses)
                {
                    Console.WriteLine("\t{0}", address.City);
                }
            }
        }
        Console.Write("Press Enter...");
        Console.ReadLine();
    }

当我将SQL Profiler附加到数据库实例时，可以看到执行了以下SQL：

SELECT 
[Extent1].[ContactID] AS [ContactID], 
[Extent1].[FirstName] AS [FirstName], 
[Extent1].[LastName] AS [LastName], 
[Extent1].[Title] AS [Title], 
[Extent1].[AddDate] AS [AddDate], 
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [dbo].[Contact] AS [Extent1]
WHERE N'Robert' = [Extent1].[FirstName]

但是没有查询地址表的迹象（即使存在地址）。

当我连接了Entity Framework Profiler之后，我能够看到两者：

SELECT [Extent1].[ContactID]    AS [ContactID],
       [Extent1].[FirstName]    AS [FirstName],
       [Extent1].[LastName]     AS [LastName],
       [Extent1].[Title]        AS [Title],
       [Extent1].[AddDate]      AS [AddDate],
       [Extent1].[ModifiedDate] AS [ModifiedDate]
FROM   [dbo].[Contact] AS [Extent1]
WHERE  N'Robert' = [Extent1].[FirstName]

and

SELECT [Extent1].[addressID]     AS [addressID],
       [Extent1].[Street1]       AS [Street1],
       [Extent1].[Street2]       AS [Street2],
       [Extent1].[City]          AS [City],
       [Extent1].[StateProvince] AS [StateProvince],
       [Extent1].[CountryRegion] AS [CountryRegion],
       [Extent1].[PostalCode]    AS [PostalCode],
       [Extent1].[AddressType]   AS [AddressType],
       [Extent1].[ContactID]     AS [ContactID],
       [Extent1].[ModifiedDate]  AS [ModifiedDate]
FROM   [dbo].[Address] AS [Extent1]
WHERE  [Extent1].[ContactID] = 209 /* @EntityKeyValue1 */

您知道 SQL Server Profiler 为什么不能捕获 Address 表的查询吗？

谢谢。

- dragonfly

---

请确保在SQL Profiler中没有设置任何筛选器。 - Ladislav Mrnka

Answer 1

太好了！

我选择了所有事件类型，结果发现地址查询可以追踪为“RPC:Completed”，而联系人查询可以追踪为“SQL:BatchCompleted”：

exec sp_executesql N'SELECT 
[Extent1].[addressID] AS [addressID], 
[Extent1].[Street1] AS [Street1], 
[Extent1].[Street2] AS [Street2], 
[Extent1].[City] AS [City], 
[Extent1].[StateProvince] AS [StateProvince], 
[Extent1].[CountryRegion] AS [CountryRegion], 
[Extent1].[PostalCode] AS [PostalCode], 
[Extent1].[AddressType] AS [AddressType], 
[Extent1].[ContactID] AS [ContactID], 
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [dbo].[Address] AS [Extent1]
WHERE [Extent1].[ContactID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=220

- dragonfly

---

为什么会通过RPC和sp_execsql执行某些查询呢？:> - user2864740

Answer 2

当您循环内进行延迟加载时，将查询地址。通过与联系人一起单次获取它们将更加高效。要这样做，您需要：

var res = (contacts as ObjectQuery<Contact>).Include("Addresses").Execute(MergeOption.NoTracking);

- Vitaliy Kalinin

---

嗨，我推断地址是惰性加载的，尽管我还没有在EF4中控制这个功能。这就是为什么我首先启动SQL Server Profiler的原因，以查看它如何处理地址。 - dragonfly

那么，如果您在调试器中通过循环，但是Profiler仍然没有捕获任何内容？如果是这样，那么它肯定与过滤器有关。另一个可能的原因是，此ID的联系人已经在上下文中实现，并且其地址也已被加载。在这种情况下，您将从上下文中获取您的地址，而无需查询数据库。 - Vitaliy Kalinin