See SQL From EntityFramework with Collection-like Queries

Question

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

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");

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

Answer 1

check out this link... it has a tracing provider for EF4.

I'll try to give an example based on how I'm using this in a unit test.

Step 1: Register the provider

There are a couple ways to configure the provider. For my unit tests I configured the provider in code by calling the static method RegisterProvider();

[AssemblyInitialize()]
public static void AssemblyInit(TestContext context) {
    EFTracingProviderConfiguration.RegisterProvider();
}

Step 2: Create a sub-class of your entity model to provide the tracing extensions

public partial class ExtendedNorthwindEntities : NorthwindEntities {
    private TextWriter logOutput;

    public ExtendedNorthwindEntities(string connectionString)
        : base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(
                connectionString,
                "EFTracingProvider")) {
    }

    #region Tracing Extensions

    private EFTracingConnection TracingConnection {
        get { return this.UnwrapConnection<EFTracingConnection>(); }
    }

    public event EventHandler<CommandExecutionEventArgs> CommandExecuting {
        add { this.TracingConnection.CommandExecuting += value; }
        remove { this.TracingConnection.CommandExecuting -= value; }
    }

    public event EventHandler<CommandExecutionEventArgs> CommandFinished {
        add { this.TracingConnection.CommandFinished += value; }
        remove { this.TracingConnection.CommandFinished -= value; }
    }

    public event EventHandler<CommandExecutionEventArgs> CommandFailed {
        add { this.TracingConnection.CommandFailed += value; }
        remove { this.TracingConnection.CommandFailed -= value; }
    }

    private void AppendToLog(object sender, CommandExecutionEventArgs e) {
        if (this.logOutput != null) {
            this.logOutput.WriteLine(e.ToTraceString().TrimEnd());
            this.logOutput.WriteLine();
        }
    }

    public TextWriter Log {
        get { return this.logOutput; }
        set {
            if ((this.logOutput != null) != (value != null)) {
                if (value == null) {
                    CommandExecuting -= AppendToLog;
                }
                else {
                    CommandExecuting += AppendToLog;
                }
            }

            this.logOutput = value;
        }
    }

    #endregion
}

Step 3: Attach to the Log property

var context = new ExtendedNorthwindEntities("name=\"NorthwindEntities\"");

context.Log = System.Console.Out;

Answer 2

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

Edit: 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

And to review the results (Query generated using Adam Machanic's XE Code Generator)

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;