Creating history table using Entity Framework 4.1

Question

I am working on asp.net MVC 3 application and I am using codeFirst approach. I am trying to create history table or user table, Where I want to keep track of what columns were modified by user. How can I do this using EF Code First.

Do I need to do it after DataContext.savechanges ?

Please suggest.

Thanks.

Answer 1

The DbContext has a method called Entry<T>:

var entity = context.Items.Find(id);
entity.Name = "foobar";

var entry = context.Entry<Item>(entity);

entry will be of type DbEntityEntry<T> and has the properties OriginalValues and CurrentValues.

You could probably write something that will generically inspect these properties to see what has changed and then automatically insert a new record into your history table.

Either that, or use database triggers.

Answer 2

Sounds to me like you want an a filter that inherits from ActionFilterAttribute. In my case, this is the simplest example that I have. This is my model, notice that the attributes dictate the mapping to the database.

[Table("UserProfile")]
public class UserProfile
{
    [Key, DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }

    public string UserName { get; set; }
}

In my case, it was as simple as the following, although it was not historical:

public sealed class UsersContext : DbContext
{
    public UsersContext() : base("DefaultConnection")
    {
    }

    public DbSet<UserProfile> UserProfiles { get; set; }
}

public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        LazyInitializer.EnsureInitialized(ref _initializer, ref isInitialized, ref initializerLock);
    }

        public void CheckDatabase()
        {
            Database.SetInitializer<YourDBContextType>(null);

            using (var context = new YourDBContextType())
            {
                if (!context.Database.Exists())
                {
                        ((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
                }
            }

// Uses your connection string to build the following table. 
 WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "UserName", autoCreateTables: true);
        }

The end result is not only EF being code first, but also allows for your models for your views to use primitives derived from your complex entities. Now, if you have another, lets say historical, DBContext then I would recommend modifying either the text transformation file or creating a base class for your entities. Both ways allow for an easy generation of code that could insert into your table, then follow up with that entity, clone it into a historical model and save. All that being said, I am a fan of database first approaches with concentration on constraints, triggers, etc. instead of a framework.

Answer 3

I'm not sure if this is really the "appropiate" way to do it, but this is how its usually done in sql:

1. Create an extra property version of type int or something.
2. Because you probably do not want to loop every time, add another property IsLatestVersion of type bool
3. When an entity is saved, check if the entity already exists. If so, set the entity on IsLatestVersion = false.
4. Increment the version, and save the changes as new entity.