3

The requirement seems simple: when data changes, audit the changes.

Here's some important pieces to the equation:

  1. The Data in my application spans multiple tables (some cross ref. tables).
  2. My DTO is deep, with Navigation Properties conditionally populated.
  3. When loaded, I copy the original DTO with its "original values".
  4. When saved is requested, the original DTO contains the changes.
  5. Ideally, foreign keys will read like useful text not Id numbers.

Unlike TFS' cool history feature, mine seems more complicated because of the many related tables and conditional child entities.

I see three possibilities (so far):

  1. I could use C# to reflect the objects and create a before/after record.
  2. I could use triggers in SQL 2008R2 to catch changes and coalesce a before/after record.
  3. I could store the raw before/after objects and let SQL 2008R2 parse them.

Please note: Right now, I seems to me that SQL 2008R2's CDC is far too heavy of an option. I am really looking for something I can build, but I admit my mind is open to anything right now.

My question

Before I get started building this: How does everybody else handle auditing a complex EF DTO?

Is there a low(ish)-tech solution available?

Thank you in advance.

Related, but not-completely-related questions already on StackOverflow: Implementing Audit Log / Change History with MVC & Entity Framework and Create Data Audit in SQL Server and https://stackoverflow.com/questions/5773419/how-to-audit-many-to-many-relationship-in-entity-framework and Maintaining audit log for entities split across multiple tables and Linq to SQL Audit Trail / Audit Log: should I use triggers or doddleaudit? do not provide an answer.

Community
  • 1
  • 1
Jerry Nixon
  • 29,446
  • 13
  • 110
  • 219
  • 1
    Not sure why you don't list SQL Server's native auditing feature as a choice. – Craig Stuntz Sep 14 '11 at 17:06
  • Sorry @Craig, you made an erroneous assumption about my DBAs. It's simply outside of their current skillset and would create a nightmare (and endless delay) for the Solutions team to require it from IT. Just a reality here, that's why I excluded it. – Jerry Nixon Sep 14 '11 at 17:35
  • Well, even if they don't already know it, it's at least documented. – Craig Stuntz Sep 14 '11 at 18:42

4 Answers4

2

IF audit is a real requirement I would opt for the trigger solution... since the other methods have several "shortcomings":

  • "blind" to any changes happening through other means than your application
  • if you make some code changes and forget about adding the audit code the audit trail gets "blind spots"

The trigger-based solution can be secured so that only special users can even see the audited data...

I usually work with Oracle but from my experience in such situations: allow the app only SELECT rights via Views , any insert/delete/update should be done via Stored procedures and audit trail should be done via triggers...

Yahia
  • 67,016
  • 7
  • 102
  • 131
  • 1
    Spot on. Also, triggers can use SUSER_SNAME() to get the user ID of the person who is logged in and making the changes to rows of data, which SQL Server's built-in audit features oddly cannot do. – HardCode Sep 14 '11 at 17:44
  • No sorry, @HardCode (and Yahia), triggers have problems. Imagine a new data record on the other side of a cross reference table. It is created before the cross record in the transation which means the trigger cannot correlate the inserted record to the parent - the audit would be an orphan. It's important to operate in the transation, remember, because the deleted table is in scope. How can you solve that? – Jerry Nixon Sep 14 '11 at 21:33
  • @Jerry Nixon - if your data model is built accordingly you can correlate the audit entries just fine... if need be you can always use the described SP method to enrich the audit... we used the above with really complex models and never had the problem you describe (or perhaps I just don't understand what you mean exactly?)... – Yahia Sep 14 '11 at 21:47
  • No, @Yahia, that is simply not true. A trigger on a table is insufficient. If you have complex relationships and you update them all in the same transaction (like using EF) you simply cannot efficiently accomplish the audit. Perhaps Oracle handles transactions differently, but, no, in SQL Server a trigger solution cannot correlate the changes in one table to the changes in related tables, especially if the transaction includes a Delete statement on any other tables in the tree. CDC could work, but not triggers. – Jerry Nixon Sep 21 '11 at 22:10
1

I've recently implemented an audit log manager on top of Entity Framework. When I instantiate my audit manager, I reflect all of the entity classes, and store the property information. Then within the object context SavingChanges event, I audit all of the changes. It works great. In the case of foreign keys, I just store their Id's before and after during changes.

The nice thing about this solution is that it doesn't require any extra coding. Once you create a log manager of sort, you don't have to worry about adding new triggers, or modifying triggers when new columns are added. Any changes to your entity classes will automatically be picked up when reflecting the classes.

mservidio
  • 11,994
  • 8
  • 52
  • 82
  • No sorry, @Mservidio, EF has problems. Consider the situation of an Insert where you do not have keys. Perhaps the parent record has a key (or perhaps not) and perhaps some children are yet to have keys assigned by the database after insert. The problem is, you are creating an audit record that doesn't reference a record. And let me suggest a second problem of defaults or trigger-impacted values which are missed altogether with this approach. – Jerry Nixon Sep 14 '11 at 21:36
  • Good points @Jerry-Nixon. I coded my entity framework a month or so ago, and what you said is true. In my case having the Id's for new entity creation was not necessary, so we could use without them on create. Modifications and deletes have keys. However, you've now got me thinking that I could keep track of the saved entities and do the logging after the save, after auto generated columns and computed columns have been retrieved and then log it. – mservidio Sep 17 '11 at 05:02
  • I may now go back to revisit and see if I can do this. Of course a flaw with this approach is that the logging would occur after the changes, so it wouldn't be a guarantee log always if any issues occurred with logging. So, agreed that it's not the better approach to take if you need a rock solid audit process. It's a nice if you just need light logging that doesn't require the Id's on initial creation. To be continued... I'll provide an update if I get the Id's pulling into my audit log after saving changes in EF. – mservidio Sep 17 '11 at 05:03
  • We are attempting to create a solution with the "just before save" version of the DTO and the "just after save" version of the DTO to build the audit record from that. This seems most promising. All the data and keys we need are present in those two versions. The remaining sticky point is just that this change is not very secure. our requirements are actually for "history" not "audit" so we are good. You may not be, I do not know. – Jerry Nixon Sep 19 '11 at 22:47
1

Well, let's see. SQL Server auditing already exists, comes with tools, is probably already known by your DBAs, doesn't slow down your app, and can trace events that the application itself will never even see.

On the other hand, rolling your own in EF will allow you to audit non-SQL Server data sources. It also doesn't require EE.

Craig Stuntz
  • 123,797
  • 12
  • 247
  • 268
0

Trigger Solution, Pros:

  1. Cannot bypass the audit

Trigger Solution, Cons:

  1. Cannot audit non SQL data
  2. Cannot audit complex objects on insert

Entity Framework, Pros:

  1. Can audit everything
  2. Can audit complex objects in any state

Entity Framework, Cons:

  1. Can be bypassed (like direct-to-SQL)
  2. Requires a copy of original values

My choice is Entity Framework. Using STE makes it easier.

Either way you have to roll your own.

Jerry Nixon
  • 29,446
  • 13
  • 110
  • 219