See SQL from Entityframework with Collection-Like Queries

See SQL From EntityFramework with Collection-like Queries

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;

How do I view the SQL generated by the Entity Framework?

You can do the following:

IQueryable query = from x in appEntities
where x.id == 32
select x;

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

or in EF6:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
.ToTraceString();

or in EF6.3+:

var sql = ((dynamic)flooringStoresProducts).Sql;

That will give you the SQL that was generated.

How do you show underlying SQL query in EF Core?

Update for .NET 6 and later: EF logging is enabled by default in development.

Just add "Microsoft.EntityFrameworkCore.Database.Command": "Information" to appsettings.Development.json so it's only logged in dev mode. You typically don't want to log every query in a production app.

{
"ConnectionStrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=MyDB-2;Trusted_Connection=True;MultipleActiveResultSets=true"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
,"Microsoft.EntityFrameworkCore.Database.Command": "Information"
}
},
"AllowedHosts": "*"
}

The SQL output shows in the command window or VS output window.

Sample Image

See SQL Logging of Entity Framework Core in the official docs. In older versions, it was a bug that it didn't log by default, see this GitHub issue.

How to view generated SQL from Entity Framework?

Since you don't have Sql Profiler, your best choice would be LINQPad. You can use your existing assembly.

Click Add connection -> Use a typed data context from your own assembly -> Entity framework and select your dll.

You can write queries directly against your model (or copy-paste from your code). Select the SQL 'tab' under the query window to view the generated SQL code.

EF Core query where Were clause is a collection?

In general avoid using Any or any LINQ operator other than Contains on in memory collection like your roles (which according to the code should be of type IEnumerable<string>).

In other words, instead of

.Where(g => roles.Any(r => r == g.GroupName))

use the functionally equivalent

.Where(g => roles.Contains(g.GroupName))

The later is guaranteed to be translated to SQL IN, while the former isn't.

Interestingly and at the same time misleading is that EF Core tries to be smart and translate the former the same way as Contains, and succeeds when the containing query is executed, but not when used as part of another query.

It could be considered a current EF Core implementation defect. But the workaround/solution is (as mentioned in the beginning) to not rely on it and always use Contains.

EF: What is the SQL output when using Contains in LINQ query?

I've watched SQL profiler for you. This is what happens in Entity Framework (I quess this is wat NHibernate also will product, but I'm not sure..):

SELECT 
[Extent1].[Id] AS [Id],
[Extent1].[AccountNo] AS [AccountNo],
[Extent1].[Name] AS [Name],
[Extent1].[Description] AS [Description],
[Extent1].[IBAN] AS [IBAN],
[Extent1].[IsActive] AS [IsActive],
[Extent1].[Customer_Id] AS [Customer_Id]
FROM [dbo].[BankAccounts] AS [Extent1]
WHERE [Extent1].[Id] IN (4,5,6,7,9)

As you can see, it will become a WHERE .. IN ([the contains values])



Related Topics



Leave a reply



Submit