从EntityFramework中查看具有类集合查询的SQL

Question

我没有MS SQL（SQL Express 2008）的“完整”版本，所以我没有探查器工具。

我想查看由我的实体框架代码生成的SQL，但是我发现的所有示例都使用了

var x = from u in table
        select u;

语法类型；但我的大部分查询更像是..

var x = context.Users.Single(n => n.Name == "Steven");

语法类型。如何查看通过这种编码方式生成的SQL？有任何想法吗？

Answer 1

Express Edition是否支持扩展事件？如果是这样，它将以类似于Profiler的方式捕获语句并sp完成事件。

编辑：
我已将其更改为使用内存目标，而不是文件目标。理想情况下，取消注释这些WHERE部分，并用适当的用户名替换以仅捕获感兴趣的事件，或者您可以使用spid进行筛选WHERE (([sqlserver].[session_id]=(56)))，例如。

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='test_trace')
    DROP EVENT SESSION [test_trace] ON SERVER;
CREATE EVENT SESSION [test_trace]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
     ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
    -- WHERE (([sqlserver].[username]='Domain\Username'))
    ),
ADD EVENT sqlserver.sql_statement_completed(
     ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
     --WHERE (([sqlserver].[username]='Domain\Username'))
     )
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
MAX_DISPATCH_LATENCY = 1 SECONDS, MAX_EVENT_SIZE = 0KB, 
MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)

ALTER EVENT SESSION [test_trace] ON SERVER STATE = START

并查看结果（使用Adam
Machanic的XE代码生成器生成的查询
）

DECLARE 
    @session_name VARCHAR(200) = 'test_trace'

SELECT 
    pivoted_data.* 
FROM 
( 
 SELECT MIN(event_name) AS event_name,
     MIN(event_timestamp) AS event_timestamp,
     unique_event_id,
     CONVERT ( BIGINT, MIN (
         CASE
             WHEN d_name = 'cpu'
             AND d_package IS NULL
             THEN d_value
         END ) ) AS [cpu],
     CONVERT ( BIGINT, MIN (
         CASE
             WHEN d_name = 'duration'
             AND d_package IS NULL
             THEN d_value
         END ) ) AS [duration],
     CONVERT ( BIGINT, MIN (
         CASE
             WHEN d_name = 'object_id'
             AND d_package IS NULL
             THEN d_value
         END ) ) AS [object_id],
     CONVERT ( INT, MIN (
         CASE
             WHEN d_name = 'object_type'
             AND d_package IS NULL
             THEN d_value
         END ) ) AS [object_type],
     CONVERT ( DECIMAL(28,0), MIN (
         CASE
             WHEN d_name = 'reads'
             AND d_package IS NULL
             THEN d_value
         END ) ) AS [reads],
     CONVERT ( VARCHAR(MAX), MIN (
         CASE
             WHEN d_name = 'session_id'
             AND d_package IS NOT NULL
             THEN d_value
         END ) ) AS [session_id],
     CONVERT ( INT, MIN (
         CASE
             WHEN d_name = 'source_database_id'
             AND d_package IS NULL
             THEN d_value
         END ) ) AS [source_database_id],
     CAST((SELECT CONVERT ( VARCHAR(MAX), MIN (
         CASE
             WHEN d_name = 'sql_text'
             AND d_package IS NOT NULL
             THEN d_value
         END ) )  AS [processing-instruction(x)] FOR XML PATH('') ) AS XML) AS [sql_text],
     CONVERT ( DECIMAL(28,0), MIN (
         CASE
             WHEN d_name = 'writes'
             AND d_package IS NULL
             THEN d_value
         END ) ) AS [writes]
 FROM
    ( 
        SELECT 
            *, 
            CONVERT(VARCHAR(400), NULL) AS attach_activity_id 
        FROM 
        ( 
            SELECT 
                event.value('(@name)[1]', 'VARCHAR(400)') as event_name, 
                event.value('(@timestamp)[1]', 'DATETIME') as event_timestamp, 
                DENSE_RANK() OVER (ORDER BY event) AS unique_event_id, 
                n.value('(@name)[1]', 'VARCHAR(400)') AS d_name, 
                n.value('(@package)[1]', 'VARCHAR(400)') AS d_package, 
                n.value('((value)[1]/text())[1]', 'VARCHAR(MAX)') AS d_value, 
                n.value('((text)[1]/text())[1]', 'VARCHAR(MAX)') AS d_text 
            FROM 
            ( 
                SELECT 
                    ( 
                        SELECT 
                            CONVERT(xml, target_data) 
                        FROM sys.dm_xe_session_targets st 
                        JOIN sys.dm_xe_sessions s ON 
                            s.address = st.event_session_address 
                        WHERE 
                            s.name = @session_name 
                            AND st.target_name = 'ring_buffer' 
                    ) AS [x] 
                FOR XML PATH(''), TYPE 
            ) AS the_xml(x) 
            CROSS APPLY x.nodes('//event') e (event) 
            CROSS APPLY event.nodes('*') AS q (n) 
        ) AS data_data 
    ) AS activity_data 
    GROUP BY 
        unique_event_id 
) AS pivoted_data;