Thursday, August 11, 2011

Viewing SQL statements created by Entity Framework with SQL Server Compact

Sometimes it can be useful to be able to inspect the SQL statements generated by Entity Framework against your SQL Server Compact database. This can easily be done for SELECT statements as noted here. But for INSERT/UPDATE/DELETE this method will not work. This is usually not a problem for SQL Server based applications, as you can use SQL Server Profiler to log all SQL statements executed by an application, but this is not possible with SQL Server Compact.

This forum thread contains an extension method, that allows you to log INSERT/UPDATE/DELETE statements before SaveChanges is called on the ObjectContext. I have updated and fixed the code to work with SQL Server Compact 4.0, and it is available in the updated Chinook sample available below in the ObjectQueryExtensions class in the Chinook.Data project.

You can now use code like the following to inspect an INSERT statement:

using (var context = new Chinook.Model.ChinookEntities())
context.Artists.AddObject(new Chinook.Model.Artist { ArtistId = Int32.MaxValue, Name = "ErikEJ" });
string sql = context.ToTraceString();

The “sql” string variable now contains the following text:

--=============== BEGIN COMMAND ===============

declare @0 NVarChar set @0 = 'ErikEJ'

insert [Artist]([Name])

values (@0)

; select [ArtistId]

from [Artist]

where [ArtistId] = @@IDENTITY


--=============== END COMMAND ===============

This statement reveals some of the magic behind the new support for “server generated” keys with SQL Server Compact 4.0 when used with Entity Framework 4.0. SQL Server Compact is “tricked” into executing multiple statements in a single call.


Denis Gladkikh said...

You can easily trace all SQL queries (for SQL CE as well) with Profiling Tools from VS Ultimate, just turn on "Enable Tier Iteraction Profiling".

ErikEJ said...

Denis: Thanks for the info, but we do not all own a copy of Ultimate, in addition, my approach allows you to log statement from within your app (at runtime).

Chris said...

Super helpful! Do you have an update of this code that works with EF6?

ErikEJ said...

Chris: Nope, I cannot make the current code work with EF6, the team seems to be working on enabling it: (please vote for it)