Auditing DB actions using MVC 4 and Entity Framework

This is a post from sometime last year that for some reason I never published.

This post describes an approach for an ASP.Net MVC application with a database-first EF Model.  I was using MVC 4 and EF 4.3.  The project this was for had some requirements and standards that have to be met about the degree of auditing on at the DB level. Simply overriding the EF SaveChanges() method to catch all actions will not suffice as the DB would likely be accessed by other means outside of the MVC/EF application. So table based triggers was the way to go but how to pass the username through to the DB?

The approach described below builds of off concepts outlined in the following articles:

1. http://lgsong.blogspot.ca/2012/01/contextinfo-and-entity-framework.html This article gives the general idea of what I'm doing here.  
2. http://jmdority.wordpress.com/2011/07/20/using-entity-framework-4-1-dbcontext-change-tracking-for-audit-logging/ See this article if you're using a Code-First approach (this was not an option for us).
3. http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx The code in this article was full of syntax errors and was missing spaces and required a little clean up in order to work with it but the ideas are good.  I adapted his approach of dynamically adding triggers to all the tables to work with the Audit table schema we were using.

The high level description of the approach is as follows:

1. Create Audit_Log and Audit_Comment tables in your DB
2. Add a stored procedure that takes a username and a comment string and inserts them into the comment table saving the generated comment_id and username in the database CONTEXT_INFO.
3. Add insert, update and delete triggers to each table that will first extract the comment_id and username from the ContextInfo() and then use those when inserting the audit details into the Audit_Log table.

Other Suggestions

Use an XML column to reduce the number of inserts.
Instead of storing before and after values in each audit entry row, the before values can be retrieved from the previous entry (inserts are also audited) although this makes queries on the audit data slightly complicated. Could also store only the columns that changed in the XML....

Threading Woes

Until recently I've gotten away with only looking at or making minor tweaks to VB.Net code. I've now been tasked with modifying a web service that involved reviving some cached values in a background thread so the user wasn't left waiting for the task to finish. If the values were cached the service responded well but once the cache expired there was a long wait (minutes) for any request that came in while the cache was rebuilt. The solution, until a replacement service is completed, was to preemptively rebuild the cache in a thread prior to expiry. The solution involved locks to ensure only one thread is rebuilding the cache at once and thus prevent cycles spent needlessly rebuilding the cache multiple times. Locks in VB are created using SyncLock which is like lock in C#. The challenging bit for me (and I now know way more about VB than I ever intended to) was that the Static keyword in VB does not work the way you would expect coming from a C# perspective (or any other perspective I've encountered for that matter) and can only be applied to local variables and properties. It turns out what I really wanted was a Private Shared ReadOnly object although it took me some time and plenty of tinkering to come to that conclusion.

Also, despite the great thread debugging tools available in Visual Studio, print statements proved invaluable in actually seeing what order things were happening in and seeing when the locks were actually working or not. Sometimes the simplest approach is the most effective.

jQuery UI Tabs issue in MVC 4

I was trying to set up the jQuery UI Tabs sample in an ASP.Net MVC 4 application and got the following error in the console:

Uncaught TypeError: Object #<Object> has no method 'tabs'

It turned out that jQuery was not yet loaded at the time the .tabs() method was being called.  Scripts must be loaded in the correct order or the code they're referencing may not be available.  I had the bit in the document ready block but was still having a problem. The problem in the MVC case seemed to be the mix of using both MVC bundles and script tags. There is likely a proper way to do it if I were to read more about bundles and when they're loaded, etc. 

I decided to put all the script resources into bundles and loaded them that way and I left the bit of code calling $('#tabs').tabs() in a script tag. Still no luck. So I deleted that script tag, loaded my page, made sure all scripts were available and then in the chrome javascript console I entered $('#tabs').tabs() and BAM, the tabs appeared.  This proved that the script fragments were not being run in the order I had expected so I put the code in a separate js file and loaded it in a bundle as well and everything worked.

So if you're using Bundles, it appears your best bet is to use them across the board and not mix and match with inline script tags.

Understanding the Resistance to Agile

I have been reading some architecture documentation for a set of services that are part of a much larger system. In one section of the document, after a brief (and incorrect) description of Entity Framework, I encountered this paragraph:

This framework was chosen to support agile development. The developer does not need to write any data access logic, other than creating the entity data model, and the framework was built to work directly with the Windows Communication Foundation DataService class.

Firstly, this statement misconstrues the meaning of Agile Development. Agile Development has nothing to do with what code is written or which framework is chosen. Agile development is about enabling the developers and about being able to develop amidst a rapidly changing environment towards an often incorrectly or partially defined target. The first value defined by the agile manifesto (http://agilemanifesto.org/) states that we value "Individuals and interactions over processes and tools" yet this statement is all about choosing tools (EF and WCF).


No wonder there is resistance to Agile Development when there are people spreading falsehoods like this around.

I still can't believe what I've just read... not to mention I read it in an overly "comprehensive" 67 page document.  As if a description of EF was necessary; a link to the official Microsoft documentation on EF would have sufficed.

TFS... meh.

I've recently switched jobs and am settling in to a new work environment; learning new practices and procedures. One new thing I've been exposed to here is using Team Foundation Server for source control. I had heard it was a huge improvement over Visual Source Safe (which wouldn't take much to accomplish) but that was about the only comment I'd get from people using it.  Now that I've had a little bit of time to try it out I'm beginning to form opinions.  My first impression was that TFS has a lot more to offer than just source control and if a team were taking advantage of those additional features (like Team Build, Project Management Features, etc.) then I could see the advantages of it.  Unfortunately, just as a VCS tool it leaves something to be desired.  I'm planning to leverage Team Build and bring some continuous integration into play in the new environment here but that will be fuel for another post.

TFS First Impressions

Like the title of this post says, my general first impression was just that, "meh".  There was still a flavour of VSS with all the locks and how I have to actually "check out" a file before I can edit it.  If there is an image or a word document in the solution, for example, that I need to modify for some reason, I have to first, check the file out in Visual Studio, then open the file from windows explorer using word or whatever third party application I need to make the change after which I then check in the changes from within Visual Studio.  It all just seems so inconvenient.  I don't know how many times I've opened a file in the past week, made some changes, and tried to save them only to be told the file is read-only...  I know it's a habit and I'll eventually be broken of it, but it's still an extra step.  It slows development down.  Thankfully, Visual Studio is smart enough to check a file out for me when I start to edit it.  But if I want to open some javascript files in a text editor because I like the way that editor handles jslint or code completion or feature X, I still have the same problem.

Now the above is going to prompt comments about how there are tools like Team Explorer that have shell extensions that will let me do it from the windows explorer context menu and my reply to them would be that I've installed that but it's still a bit annoying to use. Didn't even work at first and my icon overlays don't seem to be working either... they're always the same green triangle, they don't ever change...

Merging is SLOOOOOOWWWW....

Merging conflicts is a pain which probably is why people try to work in a way to avoid conflicts at any cost. The built in merge/compare tools are lackluster and feature poor and once you've completed the merge, saving any changes takes much longer than it should and during this save operation, the strangest thing happens, all the Visual Studio windows are redrawn in the display.  They go all white for a second or two and then slowly everything comes back bit by bit. I'm not sure why this has to happen but it does.  Now I understand we're not using the most recent version of TFS but it is still strange behaviour that renders the system unusable for several seconds.

Shelving/Unshelving: great in concept, annoying in practice.

When I first read about shelving changes in TFS I thought, "oh, just like stashing in git" which is a git feature I admittedly haven't used a whole lot either. At any rate, it sounded like a useful feature and thought I'd try it out. I had been working on some code and needed to switch to something else so I figured I'd give it a go; they weren't really huge changes so I could afford to do them again if the it didn't work as I expected.  I shelved the changes, did the other work and checked it in. I didn't unshelve my previous changes immediately and came back to it a day or two later.  The unfortunate thing was that my newer changes did not work with the shelved changes and the shelved changes had no connection to the revision on which they were made which basically rendered them useless and I ended up just doing them all over again in context of the more up to date code.  I found it to be a frustrating experience mostly due to my expectations not being met.  I'm not one to give up so easily and so have continued to play with them on occasion and they have some use as long as you can work within the limitations of the feature.  I think most of my disappointment comes from the the feature not being as cool or useful as it sounded when reading about it in the documentation.