Auditing is generally important in most databases because it is important to know who changed data and when.  How auditing data is stored depends upon the system requirements but in general the date/time and user who made a change is important.  SQL Server already provides the infrastructure to identify the who and what.  Setting up EF to provide this information is straightforward once you know how EF works.  In this post I’ll illustrate a simple approach we’ve been using in web applications for over a year with no issues and very little effort.  

Database Structure

The focus of this article is on EF and the user context so we will use a simple auditing approach where only the create and last modified information is saved.  It would be straightforward to expand this to store all changes in an auditing table.  For our purposes we’ll assume a simple table of products where the audit columns are stored directly on the table. 

CREATE TABLE [dbo].[Products]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] VARCHAR(100) NOT NULL, 
    [Price] MONEY NOT NULL, 
    [CreateDate] DATETIME NOT NULL, 
    [CreateUser] VARCHAR(128) NOT NULL, 
    [LastModifiedDate] DATETIME NULL, 
    [LastModifiedUser] VARCHAR(128) NULL
)

The CreateDate and CreateUser columns are set when an insert occurs via default values.  The LastModifiedDate and LastModifiedUser columns are set when an update occurs via a trigger.  The dates are set to the current UTC date.  The user will be set based upon some rules discussed next.

Getting User Context

In SQL the typical way to get the user context (which includes the user’s name) is to use SUSER_NAME() or equivalent.  This returns back the user name of the user associated with the current SQL session.  This information is generally passed as part of the connection string.  If your application is using Integrated Security (Windows authentication) and the application is running on a user’s desktop then this will work just fine. 

For external web applications this does not work well for the following reasons.

• Each user of the application would need their own Windows login
• Each user would also need a SQL CAL or the database would need to have per-server licensing.
• Scalability would suffer because each user of the application would get their own connection to the database

Even for internal web applications a shared database account is often used for licensing and scalability reasons.  The result is that getting the current user would return the shared account rather than the actual user name.  For web applications the current SQL session is not sufficient.

Another approach that is often used is to pass parameters to the database during updates (generally stored procedures) that include the user information.  While this is certainly easy to do there are some problems with this approach as well.

• Auditing data is mixed in with the functional data
• Each sproc would need to handle auditing rather than centralizing it in a trigger or other mechanism
• Clients are responsible for passing the correct data which is error prone if it is being done all over the application
• Maintenance is harder because the auditing is riddled throughout the application and database

Fortunately SQL has a better approach.  CONTEXT_INFO is a small value (up to 128 bytes) that can be associated with a session.  Each session gets its own value and the value is completely up to the database and application to manage.  The application is responsible for setting the value each time it connects to the database and the database can then use whatever value was stored.  To simplify things a stored procedure can be used to set the value.

CREATE PROCEDURE [dbo].[SetUserContext]
    @userName VARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @context VARBINARY(128)
    SET @context = CONVERT(BINARY(128), @userName)

    SET CONTEXT_INFO @context
END

To use the context value simply call CONTEXT_INFO().  It might be a good idea to fall back to SUSER_NAME if it is not available.  For simplicity this can be wrapped up in a user-defined function that can be used both as the default value for the create column and for the last modified value in the trigger.

CREATE FUNCTION [dbo].[GetUserContext] ()
RETURNS VARCHAR(128)
AS
BEGIN
    RETURN COALESCE(CONVERT(VARCHAR(128), CONTEXT_INFO()), SUSER_NAME())
END

To set the create column values, default values need to be added to the columns.

[CreateDate] DATETIME NOT NULL DEFAULT getutcdate() , 
[CreateUser] VARCHAR(128) NOT NULL DEFAULT dbo.GetUserContext(), 

An update trigger needs to be set up to use the context information for the modified columns. 

CREATE TRIGGER [dbo].[trg_Products_Update]
    ON [dbo].[Products] AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON

    UPDATE Products
    SET
        LastModifiedUser = dbo.GetUserContext(),
        LastModifiedDate = GETUTCDATE()
    FROM Products p INNER JOIN inserted i ON p.Id = i.Id
END

EF Model

Now that the database is set up we can define a simple POCO to support it.  It is important to note that the audit columns need to be marked as database generated so the application will not save any changes made to them.

[Table("Products")]
public class Product
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required]
    [StringLength(100)]        
    public string Name { get; set; }

    [Required]
    public decimal Price { get; set; }
                
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string CreateUser { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime CreateDate { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public string LastModifiedUser { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime? LastModifiedDate { get; set; }
}

The corresponding context would look like this.

public class SampleDbContext : DbContext
{
    public SampleDbContext ( string connectionString ) : base(connectionString)
    {
    }

    public DbSet<Product> Products { get; set; }

    public string UserName { get; set; }
}

Notice the property that was added for user name.  Finally some sample code to insert a record.

using (var ctx = CreateContext())
{                
    var item = new Product() { Name = "Router", Price = 200 };
                
    ctx.Products.Add(item);
    ctx.SaveChanges();
};

Setting the Context in EF

Calling a stored procedure in EF is not too difficult.  But there is a problem with how EF works.  Calling a stored procedure using ExecuteSqlCommand opens a connection to the database, runs the command and then closes the connection.  When EF saves changes inside SaveChanges it will open a new connection to the database.  As mentioned earlier the context is per session.  Since EF will not be using the same session for each call we have to set the user context using the same connection that the changes will use.  (Note: I believe versions of EF after 5 may provide better support for opening a connection early). 

Fortunately EF is pretty smart so it is possible to open the connection explicitly inside SaveChanges and EF will not open a new connection.  However ExecuteSqlCommand will still close the connection when it is done so we cannot use that.  Instead we have to manually call the stored procedure after opening the connection and before performing the actual update.  The user name will come from the property we added earlier.

public override int SaveChanges()
{           
    SetUserContext();

    return base.SaveChanges();
}

private void SetUserContext ()
{
    if (String.IsNullOrWhiteSpace(UserName))
        return;

    //Open a connection to the database so the session is set up
    this.Database.Connection.Open();

    //Set the user context
    //Cannot use ExecuteSqlCommand here as it will close the connection
    using (var cmd = this.Database.Connection.CreateCommand())
    {
        var parm = cmd.CreateParameter();
        parm.ParameterName = "@userName";
        parm.Value = UserName;

        cmd.CommandText = "SetUserContext";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.Add(parm);

        cmd.ExecuteNonQuery();
    };
}

Now whenever changes are saved in EF we will set the user context prior to saving any changes.  The audit triggers will get the user information set through the EF context and set the audit columns appropriately.  The only real question now is where to set the user name information.  For most applications this will likely be done when the context is created through whatever factory is being used (often an IoC).  The user information will likely come from the thread’s identity information but it can be set to anything. 

static SampleDbContext CreateContext ()
{
    var ctx = new SampleDbContext("SampleDb");

    //Get from thread identity or something
    ctx.UserName = "Bob";

    return ctx;
}

Drawbacks

One drawback to this approach is it only works for model changes.  If your application modifies data using stored procedures that do not involve a call to SaveChanges then the context will not be set.  One workaround would be to create a general method in the context that executes stored procedures but also sets the user context first.  The same rule applies though that the database connection has to remain open for both calls in order to be effective.

Another drawback is that this specific implementation is SQL Server specific.  Of course the same concept can be applied to other databases as well but the code will likely have to be modified accordingly.

Yet another issue to be aware of is that the context is limited to 128 bytes.  A user name in SQL can be up to 128 Unicode characters.  Therefore if you are using Unicode user names then the full name may not fix.