The requirement seems simple: when data changes, audit the changes.
Here's some important pieces to the equation:
- The Data in my application spans multiple tables (some cross ref. tables).
- My DTO is deep, with Navigation Properties conditionally populated.
- When loaded, I copy the original DTO with its "original values".
- When saved is requested, the original DTO contains the changes.
- 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):
- I could use C# to reflect the objects and create a before/after record.
- I could use triggers in SQL 2008R2 to catch changes and coalesce a before/after record.
- 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.