Is EF or SQL the better choice to audit data changes?

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 开发者_如何学JAVAbecause 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.

---

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...

---

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.

---

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.

---

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.