Deep Dive on Table Auditing

Table auditing is one of the easiest and at the same time most useful features of the framework. This is a very nice feature for governmental compliance, financial-systems logging, or just tracking how your data changes. The concept is quite simple. When table auditing is turned on, all changes to a database row is logged into a shadow table. The shadow table is a mimic of the real table with some additional fields for tracking. Each time a CRUD operation is performed, the action is also logged via database triggers to the shadow table. This system is completely autonomous and there is nothing more you need to do from a programmatic standpoint. Simply set the AllowAuditTracking field to true and the table will be tracked.

Now that is simple on the surface but there is a layer of complication going on in the background of course. First a shadow table is created. each shadow table is named "AUDIT[TableName]". So looking at your database it is easy to determine which are the audit tables. Keep in mind that you should not manually change these tables as they are maintained by the framework.


When you generate the Installer project, all database scripts are created or changed. Enabling table audits automatically creates scripts for the triggers and shadow table creation or updating. This is a simple enough concept. Where this functionality really shines is the API on top. You do not need to query these tables directly. The DAL generator creates methods that query this data when necessary.

If a table is marked for auditing then additional API methods are created. There are two sets of methods: instance and static. The instance methods are generated on the actual business object. This allows you to get audit records for a specific entity just by calling its "GetAuditRecords" method. The records returned are based on the primary key of the actual business object. There are various overloads of course. You can get all audits for a primary key or paginated and limit by date as well.

using (var context = new MyEntities())
    //Get all customers
    var customerCollection = context.Customer.ToList();

    //Get the first customer in the list
    var customer = customerCollection.FirstOrDefault();

    //Get all audit records for this 1 customer
    var allrecords = customer.GetAuditRecords();

    //Get all paged records (page 2, 10/page)
    var pagedrecords = customer.GetAuditRecords(2, 10);

    //Get paged records (page 2, 10/page) 
    //for all of yesterday
    var pagedrecords2 = customer.GetAuditRecords(2, 10, 
	DateTime.Now.Date.AddDays(-1), DateTime.Now.Date);

    //Loop and show all audit records in output window
    foreach (var audit in allrecords)
		audit.AuditDate.ToShortDateString() + " ," +
		audit.AuditType.ToString() + ", " +
The other way to get records is using the static methods. These allow you to get audit records without actually having to load a business object. This is the only way to get audits of deleted records since you cannot load the business object in this case. The syntax is almost identical except you use the Audit object. For the Customer object its associated audit object is named CustomerAudit.

//Get all audit records for customer 1
var allrecords = CustomerAudit.GetAuditRecords(1);

//Get paged records (page 2, 10/page) 
//for all of yesterday for customer 1
var pagedrecords = CustomerAudit.GetAuditRecords(2, 10, 
	DateTime.Now.Date.AddDays(-1), DateTime.Now.Date, 1);
Each audit record has the date it was created and the type of operation the record represents: add, edit, or delete. You can order the returned record list using LINQ in any way you wish; however by default the items are returned newest to oldest. These records provide all the changes that have occurred on a specific database row across time. You can reconstruct the change line from this record history. However there is no need to do this manually.
Each strongly-typed Audit class has a static method on it named "Compare", which returns a set of differences. You can enumerate though the collection to get an original value and a new value for each field that has changed. Only changed fields are in the difference list.

//Get all audit records for customer 1
var allrecords = CustomerAudit.GetAuditRecords(1);

//Assume there are 2 update records
CustomerAudit ca2 = allrecords.First();
CustomerAudit ca1 = allrecords.Last();
//Compare the orig and the newer one
var result = CustomerAudit.CompareAudits(ca1, ca2);
//Loop though results and show field differences
foreach (ICustomerAuditResultFieldCompare item in result.Differences)
	var display1 = ((item.Value1 == null) ? "(NULL)" : item.Value1.ToString());
	var display2 = ((item.Value2 == null) ? "(NULL)" : item.Value2.ToString());
		"Field: " + item.Field.ToString() + " " +
		"Orig Value: " + display1 + " " +
		"New Value: " + display2 + " "

Last edited May 8, 2012 at 12:33 PM by codetools, version 5


No comments yet.