请参阅SQL从EntityFramework与集合的查询 [英] See SQL From EntityFramework with Collection-like Queries

问题描述

我没有完整的MS SQL版本（SQL Express 2008），所以我没有profiler工具。

I do not have 'full' the version of MS SQL (SQL Express 2008) so I do not have the profiler tool.

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

I want to see the SQL generated by my Entity Framework code, but all of the examples I find use the

var x = from u in table
        select u;

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

type of syntax; But most of my queries are more like ..

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

语法类型。从这种编码方式可以看到SQL生成的东西？任何想法？

type of syntax. What can I do to see the SQL generated, from this manner of coding? Any ideas?

推荐答案

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

Does Express Edition support extended events? If so this will capture statement and sp completed events in a similar way to Profiler.

编辑：我已将其更改为使用内存目标而不是文件目标。理想地取消注释 WHERE 部分，并用适当的用户名替换仅捕获感兴趣的事件，或者您可以使用 WHERE（（[sqlserver] 。[session_id] =（56）））例如。

I have changed it to use a memory target rather than a file target. Ideally uncomment the WHERE sections and replace with an appropriate user name to capture only events of interest or you can filter by spid with WHERE (([sqlserver].[session_id]=(56))) for example.

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; 

这篇关于请参阅SQL从EntityFramework与集合的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！