How can I have audit columns in a database-first approach using c#?

Question

I need to add audit columns (created by, created on, modified by and modified on) for all the tables in my MVC project (web-application). I thought of having a base class with these 4 columns and inheriting all the other classes. But I am using Database-First approach, hence EF has generated all the classes. I am not sure how can I add the audit columns and the base class inheritance.

Answer 1

Database first means you have to add all the fields to the database manually and AFAIK there is no inheritance. You could write a SQL script adding those columns. As Mahyar said. But you would still lack the inheritance.

Or you switch to code first. Then you can define an interface and handle auditing in code. As shown here. I, for example, use this interface:

    /// <summary>
    /// Adds auditing properties to an entity.
    /// </summary>
    public interface IAuditedEntity
    {
        /// <summary>
        /// Date and time of the entity's creation. Usually in UTC.
        /// </summary>
        /// <remarks>Best set via a <see cref="ITimeProvider"/>.</remarks>
        DateTime DateCreated { get; set; }

        /// <summary>
        /// Identification who created this instance.
        /// </summary>
        /// <remarks>Best set via a <see cref="ICurrentUserIdProvider"/>.</remarks>
        string CreatedBy { get; set; }

        /// <summary>
        /// Date and time of last modification. Usually in UTC.
        /// </summary>
        /// <remarks>Best set via a <see cref="ITimeProvider"/>.</remarks>
        DateTime? DateModified { get; set; }

        /// <summary>
        /// Last one modifiying this instance.
        /// </summary>
        /// <remarks>Best set via a <see cref="ICurrentUserIdProvider"/>.</remarks>
       string ModifiedBy { get; set; }
}

The user provider interface is defined like this:

    /// <summary>
    /// Interface for providing the current user's id.
    /// </summary>
    public interface ICurrentUserIdProvider
    {
        /// <summary>
        /// Get the id of the curent user.
        /// </summary>
        /// <returns></returns>
        string GetCurrentUserId();
    }

For testing purposes you now can use an ambient context/service delivering the current user or any user you need to test your logic.