Entity Framework 6: audit/track changes

Question

I have my core project in C#.

I work on a database, where some tables have the columns "user_mod" and "date_mod" for sign who and when made some mods and the same with "data_new" and "user_new".

My question: is there a way to centralize this and make this data inserted automatically, where I create the instance of dbContext?

If not, I will use an audit trail tool. I have seen some of these, but there is a problem: all of these, require some code in my model. But I don't want to write in my model, because if I have to change it, I will lost the mods. Is it possible use an audit trail for EF6 without writing in the model file(s)? How?

EDIT:

My attempt to override the saveChanges.

public partial class PieEntities : DbContext
{
    public override int SaveChanges(System.Data.Objects.SaveOptions options)
    {
        var timestamp = DateTime.Now;

        EntityState es = EntityState.Added;
        ObjectStateManager o = new ObjectStateManager();

        foreach (ObjectStateEntry entry in o.GetObjectStateEntries(EntityState.Added ))  {
            if (entry.Entity.GetType() == typeof(TabImpianti)) {
                TabImpianti impianto = entry.Entity as TabImpianti;
                impianto.DATA_INS = timestamp;
                impianto.DATA_MOD = timestamp;
                string u = mdlImpostazioni.p.UserName;
                impianto.USER_INS = u;
                impianto.USER_MOD = u;
            }
        }
        return base.SaveChanges(options);
    }
}

UPDATE: I've summarized the solution here.

Earlie

possible duplicate of Entity Framework DbContext SaveChanges() OriginalValue Incorrect. There are many more efforts in this area. Look for Entity Framework + auditing. – Meter

@GertArnold why? I don't think so. Further, that question is about EF4, two versions older of mine. – Earlie

Well, I picked a random one from a long list of hits when I search StackOverflow on this topic. It usually amounts to overriding SaveChanges, which is the same in EF4. In EF6 you may venture something in the area of command tree interceptors, but I'm not sure where that will take you. – Meter

@PieroAlberto your ObjectStateManager must come from the context: assign it from this (this as IObjectContextAdapter).ObjectStateManager – Nightie

Regarding the base call, I don't see why it would pose a problem. I think you don't need to tell the compiler you are inheriting from a DbContext again though, remove the : DbContext – Nightie

@Nightie now: foreach (ObjectStateEntry entry in (this as IObjectContextAdapter).ObjectStateManager.GetObjectStateEntries(EntityState.Added )) --> it says that ObjectStateManager doens't exist. – Earlie

@Nightie and, with and without ":dbContext", it doens't find saveChanges to override... – Earlie

Answer 1

If using EF6's DbContext you can use ChangeTracker in SaveChanges override to find added/modified entities of custom type, for example IAuditedEntity.

public interface IAuditedEntity {
  string CreatedBy { get; set; }
  DateTime CreatedAt { get; set; }
  string LastModifiedBy { get; set; }
  DateTime LastModifiedAt { get; set; }
}

public override int SaveChanges() {
  var addedAuditedEntities = ChangeTracker.Entries<IAuditedEntity>()
    .Where(p => p.State == EntityState.Added)
    .Select(p => p.Entity);

  var modifiedAuditedEntities = ChangeTracker.Entries<IAuditedEntity>()
    .Where(p => p.State == EntityState.Modified)
    .Select(p => p.Entity);

  var now = DateTime.UtcNow;

  foreach (var added in addedAuditedEntities) {
    added.CreatedAt = now;
    added.LastModifiedAt = now;
  }

  foreach (var modified in modifiedAuditedEntities) {
    modified.LastModifiedAt = now;
  }

  return base.SaveChanges();
}

Economizer

your code looks simply, but I have just tried it... I have inserted a new item in one of my tables and it didn't enter in the for of addedEntities.. how does it work? what can I do? – Earlie

Entities that you want to track/audit must implement IAuditedEntity – Economizer

my entities? but my entities are defined in the model... and I don't want to mod manually the auto-generated model. How can I do it? (excuse me the dum question) – Earlie

Then use TabImpianti in place of IAuditedEntity and replace property names to match your model. – Economizer

TabImpianti is only one of tables I want track. Do I create one interface for each tables? – Earlie

If you want to track multiple entities then its easier to create an interface and have all entities implement it. Then you can use the code above. – Economizer

But to make all entitities implement it, have I to write in the auto-generated file? or can I do it in other way? – Earlie

This solution is more for code-first approach. For Database first auto-generated entities, you will probably have to check types of each entity when enumerating the change tracker. Use ChangeTracker.Entries() instead of ChangeTracker.Entries<T>(). Then you need to check the type for each entity when enumerating. – Economizer

oook, but now, when I type "added.someAttr" it doesn't exist... can you update your answer with this example? – Earlie

ok, I've almost got it, I hope... I have put this for the new record "if (added.GetType() == typeof(TabImpianti))" and it works. But the same for the modded records "if (modified.GetType() == typeof(TabImpianti))" doesn't work, it doens't pass the if condition. Why? – Earlie

EF Database first entities are marked with the partial keyword. That makes it easy to assign attributes without interfering with generated code. Just create a new file AuditedEntries.cs containing [IAuditedEntity] public partial class TabImpianti {} – Aetiology

Just to be clear, since each entity implements the IAuditedEntity interface the properties are part of the individual entities so they are saved to the same table correct? – Koffler

Answer 2

There is one way to do it: you can create a partial class that is the same name as your object context and implement an override of the SaveChanges method. In this override you can look at all the changes that will be pushed to the DB and process them.

You can process them any way you like, in the following example I created an interface IAutoTimestampEntity that contained a creation date and a modification date. Any object of this type would be automatically updated with the time of change.

public override int SaveChanges(System.Data.Objects.SaveOptions options)
{
    var timestamp = DateTime.Now;

    foreach (var InsertedAutoTimestampEntity in ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added).Select(ose => ose.Entity).OfType<IAutoTimestampEntity>())
    {
        InsertedAutoTimestampEntity.CreationDate = timestamp;
        InsertedAutoTimestampEntity.ModificationDate = timestamp;
    }

    foreach (var UpdatedAutoTimestampEntity in ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Modified).Select(ose => ose.Entity).OfType<IAutoTimestampEntity>())
    {
        UpdatedAutoTimestampEntity.ModificationDate = timestamp;
    }

    return base.SaveChanges(options);
}

You can use the same principle, or you can look at the type of each changed entity in details. I like the declarative aspect of the interface though. It lets you expose one aspect of automation explicitly instead of letting it be done silently by the EF layer.

If you have a DbContext instead of an ObjectContext, cast your DbContext to IObjectContextAdapter to access the ObjectStateManager

Nightie

nice!! Is it also possible to see what is a delete? what are InsertedAutoTimestampEntity and UpdatedAutoTimestampEntity? – Earlie

InsertedAutoTimeStampEntity and UpdatedAutoTimestampEntity are the names I gave the entities in the enumeration, so it is just naming. – Nightie

Regarding the delete, here is the EntityState enum, you can see deleted is present as well as other states you can track: msdn.microsoft.com/en-us/library/… – Nightie

I have added the reference to System.entity. But it says that "ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added)" it isn't a static method and I have to instance it. Whty in your code isn't in this way? – Earlie

If you are using a DbContext instead of an ObjectContext, cast your DbContext to IObjectContextAdapter to access the ObjectStateManager – Nightie

@PieroAlberto - there is more than one way to do auditing. Off the top of my head, STE's, Triggers, Database level auditing (built into SQL Server), etc.. This method will get slower and slower the more objects that are added to the Local cache. It also won't track changes made through non-EF means like in SSMS or Database.ExecuteSqlCommand calls. – Tiber

@ErikFunkenbusch you are right, however unless you have windows auth forwarded to the DB this is the only way you can get things like the current user. I seldom see this kind of DB setup, is it really recommended? – Nightie

@ErikFunkenbusch I don't know STE's though, what is it? – Nightie

self-tracking entities, though they're discouraged... I don't understand your point about auth.. auth has nothing to do with auditing. – Tiber

As samy says, I also need the user logged in. @Nightie the last row, "return base.SaveChanges(options);" is red underlined... why? it says that there aren't method of saveChanges with one parameter... Now I will update the code – Earlie

@ErikFunkenbusch excuse me, but, how caon you track the timestamp and WHO did some action? – Earlie

@PieroAlberto - just make your entities auto-populate the user via the constructor. Not difficult. – Tiber

@Nightie last big problem: in the definiot row, where you write "override" it says me that there arent' method to override. why? – Earlie

You can write a trigger to log who made the changes if your users log in to SQL using Windows authentication. But if you have a web site where there is only one database user then you you should use the override of SaveChanges and somehow pass in the name of the current user: for example add a property CurrentUserName to your DbContext – Emphatic

solved with these hints and this #16005207 – Earlie

Answer 3

Addition to the answer from @AlaaMasoud.

With CreatedDate and UpdatedDate:

interface IEntityDate
{
    DateTime CreatedDate { get; set; }

    DateTime UpdatedDate { get; set; }
}

public abstract class EntityBase<T1>: IEntityDate
{
    public T1 Id { get; set; }

    public virtual DateTime CreatedDate { get; set; }
    public virtual string CreatedBy { get; set; }
    public virtual DateTime UpdatedDate { get; set; }
    public virtual string UpdatedBy { get; set; }
}

public override int SaveChanges()
{
    var now = DateTime.Now;

    foreach (var changedEntity in ChangeTracker.Entries())
    {
        if (changedEntity.Entity is IEntityDate entity)
        {
            switch (changedEntity.State)
            {
                case EntityState.Added:
                    entity.CreatedDate = now;
                    entity.UpdatedDate = now;
                    break;

                case EntityState.Modified:
                    Entry(entity).Property(x => x.CreatedDate).IsModified = false;
                    entity.UpdatedDate = now;
                    break;
            }
        }
    }

    return base.SaveChanges();
}

To handle CreatedBy and UpdatedBy I use a wrapper for DbContext like this:

public interface IEntity
{
    DateTime CreatedDate { get; set; }

    string CreatedBy { get; set; }

    DateTime UpdatedDate { get; set; }

    string UpdatedBy { get; set; }
}

public interface ICurrentUser
{
    string GetUsername();
}

public class ApplicationDbContextUserWrapper
{
    public ApplicationDbContext Context;

    public ApplicationDbContextUserWrapper(ApplicationDbContext context, ICurrentUser currentUser)
    {
        context.CurrentUser = currentUser;
        this.Context = context;
    }
}

public class ApplicationDbContext : DbContext
{

    public ICurrentUser CurrentUser;
    
    public override int SaveChanges()
    {
        var now = DateTime.Now;

        foreach (var changedEntity in ChangeTracker.Entries())
        {
            if (changedEntity.Entity is IEntity entity)
            {
                switch (changedEntity.State)
                {
                    case EntityState.Added:
                        entity.CreatedDate = now;
                        entity.UpdatedDate = now;
                        entity.CreatedBy = CurrentUser.GetUsername();
                        entity.UpdatedBy = CurrentUser.GetUsername();
                        break;
                    case EntityState.Modified:
                        Entry(entity).Property(x => x.CreatedBy).IsModified = false;
                        Entry(entity).Property(x => x.CreatedDate).IsModified = false;
                        entity.UpdatedDate = now;
                        entity.UpdatedBy = CurrentUser.GetUsername();
                        break;
                }
            }
        }

        return base.SaveChanges();
    }
    
    ...

Source:

https://mcmap.net/q/138304/-how-to-set-created-date-and-modified-date-to-enitites-in-db-first-approach

Sweetheart