0

I have an audit trailing system in my project from http://doddleaudit.codeplex.com/. As you can see on this image it records the EntityTable - which is the table name, and the EntityTableKey - which is the primary key enter image description here

I would like to associate the audit records with the tables it had recorder, then query the result in linq to sql. But the problem is if the audit table has record for orders and record for products it will never know just by the primary key, where does the record belong, thus i need to use the table name as part of the key.

So the question is: Is it possible to create a relation that will have a composite primary key that contains the table name in it?

AuditRecord to Orders
AuditRecord to Products
jekcom
  • 2,065
  • 2
  • 24
  • 34
  • It is better to split auditrecords into two separate tables. 1 for orders and other for products – Sami Oct 28 '12 at 08:01
  • @Sami - it's not how this kind of system should usually work. Audit trace displays record-after-record changes in database tables. It's just not convenient to create one "metatable" for each table in your db. – OzrenTkalcecKrznaric Oct 28 '12 at 08:28
  • What if the action was "delete" - the row will no longer exist in the non-audit table... – Damien_The_Unbeliever Oct 29 '12 at 10:20

1 Answers1

0

You could do it, but I would recommend a bit different approach.

Don't use char/varchars/nvarchar in your PK/FK, it bloats the index. I would rather create another table that will hold TableId/TableName pairs of all your tables (you can use sys.tables.object_id for your id if you wish) and have a FK in AuditRecords to it. Then establish composite key between AuditRecords and AuditRecordFields (Id, TableId).

Another thing:

  • EntityTable and AssociationTable should be of sysname type
  • AuditDate can be of type Date (available from SQL Server 2008)

EDIT:

If you like to access audit records from each object, you can create a base class for your audited objects and implement following method (beware, it's untested):

public IQueryable<AuditRecord> AuditRecords
{
    get
    {
        MyContext ctx = new MyContext();

        var ars = from ar in ctx.AuditRecords
                  where ar.EntityTable.Equals(this.GetType().Name)
                  select ar;

        return ars;
    }
}
OzrenTkalcecKrznaric
  • 5,535
  • 4
  • 34
  • 57
  • The index key is good idea, but im afraid this is not the goal i need to achieve. What i would like to get at the end is this for example: product.AuditRecord.Where(a=>a.AuditDate < DAteTime.Now) i.e. in linq to sql – jekcom Oct 29 '12 at 09:34
  • For this to happen (and avoid hacking your Linq2Sql), you can add FK between each of your entity tables and audit records (Order.AuditRecordId, Product.AuditRecordId...), but this will only get you the last audit record. However, If I remember it correctly, you can extend each of your data classes using partial and add AuditRecord property (method?) to it, where you query AuditRecord table and return the IQueriable collection filtered by table, so you can perform where filtering afterwards... – OzrenTkalcecKrznaric Oct 29 '12 at 09:51