Audit Logging of Insert with Identity Primary Key using Entity Framework DB First Approach

Question

I have a requirement to create an 'audit trail' for CRUD functions to a table but I am puzzled when it comes to the Insert part of the solution:

The issue lies with the Audit table requiring the ID of the edited row, however the ID of the table being edited is an IDENTITY and to make matters worse, the Audit table is used to audit multiple tables so a foreign key is not possible.

I've created a simplified version of the code and tables:

Tables:

    CREATE TABLE Audit
    (
        ID BIGINT NOT NULL PRIMARY KEY IDENTITY,
        TableName VARCHAR(100) NOT NULL,
        InsertedID BIGINT NOT NULL, --This is the ID of the row which has been inserted
        DateTimeInserted DATETIME NOT NULL
    )

    CREATE TABLE Customer
    (
        ID BIGINT NOT NULL PRIMARY KEY IDENTITY,
        Name VARCHAR(100),
        Surname VARCHAR(100)
    )

Code:

class Program
{
    static void Main(string[] args)
    {
        Customer customer = new Customer()
            {
                Name = "Foo",
                Surname = "Bar"
            };

        using (SampleAuditEntities dbContext = new SampleAuditEntities())
        {
            InsertAuditTool insertAuditTool = new InsertAuditTool(dbContext);                

            insertAuditTool.TestInsert(customer);

            dbContext.SaveChanges();
        }

        Console.WriteLine("Customer ID = " + customer.ID);
        Console.ReadLine();
    }        
}

class InsertAuditTool
{
    SampleAuditEntities dbContext;

    public InsertAuditTool(SampleAuditEntities DbContext)
    {
        this.dbContext = DbContext;
    }

    public void TestInsert(Customer InsertedCustomer)
    {
        Audit audit = new Audit()
        {
            TableName = "Customer",
            DateTimeInserted = DateTime.Now, 
            InsertedID = InsertedCustomer.ID //Always 0 as it is not assigned until the change is committed.
        };

        dbContext.Customers.Add(InsertedCustomer);
        dbContext.Audits.Add(audit);
    }
}

Now normally I would take the approach of committing the Insert of the Model first, call SaveChanges() and then insert into the audit table but it would be bad practice to potentially allow an insert to be committed and have the audit fail as every insert must be audited (and vice versa).

So my question is, how can I accurately record the new ID of the Inserted row without having 2 separate transactions. Is this approach even possible? If not, what are my options?

Thanks in advance!

This just **screams** for a **trigger**! Have a trigger for `INSERT` and in that trigger, write the newly inserted data into your `Audit` table; at that point, the newly inserted `ID` **will** be available to the trigger code — marc_s, Jan 10 '14 at 11:30
@marc_s Thanks for the suggestion but I've considered using a Trigger and haven't because I also log the user which performed the change (I now realise that I should've pointed this out). The issue is that the application makes use of SQL Server Authentication so unless I'm missing something, there's no way of indicating who made the change. (I know that screams bad practice but this is beyond my power to change at this point) — Gareth, Jan 10 '14 at 11:42
Well, the value of an `IDENTITY` column is only available until **AFTER** that `INSERT` has actually happened. So I guess you'll need to change your auditing to be performed **after** `.SaveChanges()` - otherwise you cannot get that identity value..... — marc_s, Jan 10 '14 at 11:45
Typically, I found what seems to be the answer after posting this question. http://stackoverflow.com/questions/17523568/entity-framework-retrieve-id-before-savechanges-inside-a-transaction — Gareth, Jan 10 '14 at 12:17
And that response **also** says the same thing: you can **ONLY** get the identity value reliably **AFTER** the row has actually been inserted — marc_s, Jan 10 '14 at 12:19
Whoops wrong link.... Here's the correct one: http://stackoverflow.com/questions/6028626/ef-code-first-dbcontext-and-transactions BTW thanks for your help @marc_s - it is appreciated! — Gareth, Jan 10 '14 at 12:22

score 0 · Accepted Answer · edited May 23 '17 at 12:21

So (typically) only after posting this question and performing some more 'googling' with different terms, I've managed to find an answer to a similar question on Stack Overflow.

EF Code First DBContext and Transactions

The answerer recommends making use of TransactionScope which has worked well for me. This has brought me to the following solution:

Note: I had to add a reference and using statement to System.Transactions

Code:

using System;
using System.Text;
using System.Transactions;

namespace RefSandBoc
{
class Program
{
    static void Main(string[] args)
    {
        try
        {
            Customer customer = new Customer()
                {
                    Name = "Foo",
                    Surname = "Bar"
                };

            using (SampleAuditEntities dbContext = new SampleAuditEntities())
            {
                using (var scope = new TransactionScope(TransactionScopeOption.Required,
                    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
                {
                    InsertAuditTool insertAuditTool = new InsertAuditTool(dbContext);

                    insertAuditTool.TestInsert(customer);

                    //throw new Exception("Test Exception");
                    scope.Complete();
                }
            }

            Console.WriteLine("Customer ID = " + customer.ID);
            Console.ReadLine();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            Console.ReadLine();
        }
    }
}

class InsertAuditTool
{
    SampleAuditEntities dbContext;

    public InsertAuditTool(SampleAuditEntities DbContext)
    {
        this.dbContext = DbContext;
    }

    public void TestInsert(Customer InsertedCustomer)
    {
        dbContext.Customers.Add(InsertedCustomer);
        dbContext.SaveChanges();

        //throw new Exception("Test Exception");

        Audit audit = new Audit()
        {
            DateTimeInserted = DateTime.Now,
            InsertedID = InsertedCustomer.ID
        };

        dbContext.Audits.Add(audit);
        dbContext.SaveChanges();

        //throw new Exception("Test Exception");
    }
}
}

If I uncomment either of the exceptions, the code behaves the way I expect. The only down-side to this approach is that once you call the TransactionScope.Complete() method, the changes will be committed regardless of whether or not there are other concurrent transactions within the DBContext.

For further learning, there is also an excellent article on Code Project which details the use of TransactionScope: http://www.codeproject.com/Articles/690136/All-About-TransactionScope

Audit Logging of Insert with Identity Primary Key using Entity Framework DB First Approach

1 Answers1