LINQ-to-SQL query auditing

4 minute read Published:

Have you ever found it a pain to try to debug the SQL query generated by LINQ-to-SQL? For my employer, Kaplan, I’ve designed a SQL query auditing “framework” that allows us to audit all of the LINQ generated SQL queries from our data repository implementation class. (I use the term “framework” here lightly because it is no more than a few “shim” methods implemented in an abstract base class that the repository implementation class derives from.)

Let’s take the following code snippet: var query = from pe in db.EiProgramEnrollment join ce in db.EiClassEnrollment on pe.ProgramEnrollmentID equals ce.ProgramEnrollmentID join cs in db.EiClassSchedule on ce.ClassID equals cs.ClassID join cr in db.EiClassRoom on cs.ClassRoomID equals cr.ClassRoomID join cb in db.EiClassBuilding on cr.ClassBuildingID equals cb.ClassBuildingID where pe.StudentID == studentId select new { cs, cr, cb }; return AuditEnumerable(query, m => new ClassScheduleDetails(mapClassSchedule(m.cs),mapClassRoom(m.cr),mapClassBuilding(m.cb)));

This is an actual method implementation we use in our system and it conforms to a set of guidelines necessary to enable this auditing support. The main concept that should stick out here is that we don’t just return the query nor do we enumerate it here. We instead call this funny-looking AuditEnumerable method, which is part of our SQL auditing framework.

IEnumerable<U> AuditEnumerable<T, U>(IQueryable<T> query, Func<T, U> map)

The whole point of this method is to gain access to the IQueryable before anyone gets a chance to enumerate over it. Once you begin enumeration on an IQueryable (e.g. with a foreach or a .ToList()), you’ve told LINQ-to-SQL that you wish to translate that IQueryable instance into a SQL query and execute it against the database and then enumerate over the results of that SQL query.

This AuditEnumerable method “interrupts” that process and only translates the LINQ IQueryable structure into a SQL query and then stops there. When auditing mode is enabled, it returns an Enumerable.Empty<U>().

The purpose of this auditing framework is to switch OFF data access and only trace out what actual query would be executed against the database. This auditing framework produces invaluable information for both developers and DBAs.

Now, we can construct some simple user interface for capturing and browsing this auditing information per each repository method. I’ve done just that with a very crude but effective Visual Studio unit test class. Yes, it is an abuse of the unit test framework, but it is so darn handy and productive I really have no intention of removing it. I’ll most likely move it into a hosted ASP.NET page that generates these reports on-the-fly; it will probably sit next to our WCF endpoints’ SVC files on the web service host. That would make it easily accessible to DBAs, since they’re probably not too keen on cracking open Visual Studio just to do SQL code reviews.

Below is a sample output from this unit test. The unit test class is code-generated to enable the auditing mode of the data repository class and to call each data repository method that declares itself to only read data. (We have a method naming convention whereby a method that begins with “Get” must do no data writes. The code generator discovers these methods and generates auditing boilerplate code to get the SQL query text out.) Visual Studio 2010 displaying SQL query text output for the given LINQ query

Here is an example of the unit test class which produces this sort of output (trimmed for brevity / screenshot purposes): SQL auditing unit test code NOTE: I do not normally condone this indentation pattern. This is purely for display purposes only.

Basically, we’ll have a bunch of [TestMethod] decorated methods, one for each “Get” method defined on our ICoreRepository interface. Before each test method is run, a new StringWriter is created to write to a StringBuilder and that is passed to the BeginSQLAuditing method defined on the CoreDataRepository implementation class (technically it is on the abstract base class this is derived from). This tells the auditing code where to output the query text and parameter information to. Once the test method has completed, the clean-up method calls EndSQLAuditing() which gets back the StringWriter instance we originally passed in and Flush()es that. Now we can call .ToString() on the original StringBuilder that now contains the auditing information and write that to Console.Out! This shows up in the “Standard Console Output” section of the unit test results as seen above.

There is so much going on behind the scenes and lots more to discuss about the auditing framework that I would love to go into, but I think this post is long enough as it is now. I may consider writing Part 2 but don’t hold your breath. :)