EF Core 5.0

Using ToQueryString to get generated SQL

The EF team has been publishing weekly status updates since the middle of last year. Recently I have started including brief introductions for newly implemented enhancements. I'm going to experiment with blogging these brief intros to help make them more visible.

ToQueryString: A simple way to get generated SQL

EF Core 5.0 introduces the ToQueryString extension method which will return the SQL generated by EF Core when executing a LINQ query. For example, the code:

var city = "London";
var query = context.Customers.Where(c => c.City == city);


results in this output when using the SQL Server database provider:

DECLARE @__city_0 nvarchar(4000) = N'London';

SELECT [c].[Id], [c].[City], [c].[CompanyName], [c].[Email], [c].[Fax], [c].[Name], [c].[Phone]
FROM [Customers] AS [c]
WHERE [c].[City] = @__city_0

The SQL can be executed!

Notice that declarations for parameters of the correct type are also included in the output. This allows copy/pasting to SQL Server Management Studio, or similar tools, such that the query can be executed for debugging/analysis.

Execute SQL in SSMS

The SQL is database-specific

Other database providers can generate parameter declarations suitable for their ecosystems. For example, the SQLite provider generates output suitable for the SQLite CLI tool:

.param set @__city_0 'London'

SELECT "c"."Id", "c"."City", "c"."CompanyName", "c"."Email", "c"."Fax", "c"."Name", "c"."Phone"
FROM "Customers" AS "c"
WHERE "c"."City" = @__city_0

The SQL is visible in the debugger

EF Core 5 also introduces a "debug view" to easily see the generated SQL and associated expression tree in your debugger of choice. Just drill down into the EF query object and expand DebugView.

SQL in the debugger

This page is up-to-date as of January 12th, 2020. Some things change. Some things stay the same. Use your noggin.