Windows Phone Local Database tip: Viewing the SQL generated by LINQ to SQL

It may sometimes by useful to be able to see the SQL statements generated by an ORM, in order to understand better what is going on, and why things perform the way they do. With LINQ to SQL on desktop, you can assign any TextWriter object to the DataContext Log property, and this will allow you to log to a file or the console.

On Windows Phone, this gets a little bit complicated, as files must be stored in Isolated Storage, making them hard to get at, and there is no Console. This MSDN blog includes a TextWriter implementation, that writes to the console of a attached debugger, for example the Visual Studio Debug output Window. This class (which I have renamed to DebugWriter) will be part of the DataContext generated by the next SQL Server Compact Toolbox release. So now you can add the following while debugging your LINQ to SQL statements:

db.Log = new DebugWriter();

We can now try some various LINQ queries against our database to find out how they have been implemented in SQL (I am using the excellent Chinook sample database, available for download here):

db.Album.Where(al => al.AlbumId == 10).SingleOrDefault();

And the output in the Debug output window:

db.Album.FirstOrDefault(); 

Becomes:

SELECT TOP (1) [t0].[AlbumId], [t0].[Title], [t0].[ArtistId]
FROM [Album] AS [t0]

db.Album.Where(al => al.Title.StartsWith("For those%")).FirstOrDefault();

Becomes:

SELECT TOP (1) [t0].[AlbumId], [t0].[Title], [t0].[ArtistId]
FROM [Album] AS [t0]
WHERE [t0].[Title] LIKE @p0

db.Album.Skip(50).Take(25).ToList();

Becomes:

SELECT [t0].[AlbumId], [t0].[Title], [t0].[ArtistId]
FROM [Album] AS [t0]
ORDER BY [t0].[AlbumId] OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY

Notice that the last statement uses OFFSET and FETCH, a syntax only introduced in SQL Server Compact 4.0, but apparently retrofitted into the version 3.5 engine running on Windows Phone.

Happy SQL debugging!