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

go

--=============== 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.