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);
Console.WriteLine(query.ToQueryString());
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.
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.
