Track database inserts with specific data

Question

I'm maintaining ASP.NET MVC application, which talks to MS SQL database via Entity Framework.

For some time we are observing cases when entities are updated or inserted into the database with specific field null.

This field may be null, but we strongly suspect, that most of these situations should not happen. I'd like to hook some debug code to log all instances of such attempts.

The problem is that I have no idea, where should I do it.

Entity is inserted and updated in a lot of places in code;
I might override Insert and Update methods of its DBSet descendent, but I won't catch changes caused indirectly (for instance by retrieving the entity from the database, changing it and calling SaveChanges() ).

Where can I put the code, which will be able to access all write modifications to the database on a specific entity?

Not a code solution, but you could point SQL Profiler at your database and watch the commands as they whiz by. — Sam Axe, Mar 30 '17 at 09:00
http://stackoverflow.com/questions/26355486/entity-framework-6-audit-track-changes — Ray Krungkaew, Mar 30 '17 at 09:05

score 4 · Accepted Answer · answered Mar 30 '17 at 12:15

I believe you're looking for the ChangeTracking property of your DbContext.

I use a similar method to build history on entities, by capturing what has been changed/added/deleted.

To do this, you can, within your DbContext, override the SaveChanges() method and then intercept the entries that are changing.

Be sure to call base.SaveChanges(); at the end of your override to actually save any changes.

Here for example, lets say your DbContext is called MyAppDbContext

public partial class MyAppDbContext : DbContext
{
    public override int SaveChanges()
    {
         ChangeTracker.Entries().ToList().ForEach(entry =>
        {

            // entry, here, is DbEntityEntry.
            // it will allow you to see original and new values,
            // such as entry.OriginalValues
            // and entry.CurrentValues
            // You can also find its type
            // entry.Entity.GetType()


            switch (entry.State)
            {
                case EntityState.Detached:
                    break;
                case EntityState.Unchanged:
                    break;
                case EntityState.Added:
                    break;
                case EntityState.Deleted:
                    break;
                case EntityState.Modified:
                    break;
            }
        });

       // call the base.SaveChanges();
       base.SaveChanges();    
    }

}

score 0 · Answer 2 · answered Sep 10 '17 at 21:26

I think Audit.NET library with its EntityFramework extension can help you for this kind of requirement, because you will need minimal changes to your code.

For example, let say you have the following context:

public class YourContext : DbContext
{
    public DbSet<Post> Posts { get; set; }
}

And you want to audit changes when the Date field of Posts table is about to be set to NULL.

First you need to change the base class of your context to inherit from Audit.EntityFramework.AuditDbContext:

public class YourContext : AuditDbContext
{
    public DbSet<Post> Posts { get; set; }
}

Then it's just a matter of startup configuration, for example:

// Configure the event output to save logs as files
Audit.Core.Configuration.Setup().UseFileLogProvider(_ => _.Directory(@"D:\Temp"));   

// Add a custom action to filter out the events
Audit.Core.Configuration.AddCustomAction(ActionType.OnScopeCreated, scope =>
{
    var ef = scope.Event.GetEntityFrameworkEvent();
    var interested = ef.Entries.Any(e => e.Table == "Posts" 
                                    && new[] { "Insert", "Update" }.Contains(e.Action)
                                    && e.ColumnValues["Date"] == null);
    // Discard the audit event if we are not interested in (i.e. only log when Date is null)
    if (!interested)
    {
        scope.Discard();
    }
});

// Configure EF extension
Audit.EntityFramework.Configuration.Setup() 
    .ForContext<YourContext>()      // Your context class type
        .UseOptIn()                 // OptIn to include specific entities 
        .Include<Post>();           // Audit only the Post entity

With this configuration, it will be generating json files on your file system with a content similar to the following:

{
  "EventType": "YourContext",
  "Environment": {
    "UserName": "Federico",
    "MachineName": "HP",
    "DomainName": "Domain",
    "Exception": null,
    "Culture": "en-US"
  },
  "StartDate": "2017-09-10T16:11:05.7987461-05:00",
  "EndDate": "2017-09-10T16:11:10.4458419-05:00",
  "Duration": 4647,
  "EntityFrameworkEvent": {
    "Database": "Blogs",
    "Entries": [
      {
        "Table": "Posts",
        "Action": "Insert",
        "PrimaryKey": {
          "Id": 11
        },
        "ColumnValues": {
          "Id": 11,
          "BlogId": 7,
          "Content": "post content",
          "Date": null,
          "Title": "post-test"
        },
        "Valid": true
      }
    ],
    "Result": 2,
    "Success": true
  }
}

G Clovs · Answer 3 · 2022-09-09T16:40:59.623

From Asp.Net Code,

I am using this package EntityFrameworkCore.Triggered github, nuget

it's very simple to use it, like so:

public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<ApplicationContext>(options => {
                options.UseTriggers(triggerOptions => {
                    triggerOptions.AddTrigger<EntityDbTrigger>();
                });
            })
    }
    
class EntityDbTrigger : IAfterSaveTrigger<EntityDb> {
    public EntityDbTrigger (Dependency Injection...) {
    }

    public async Task AfterSave(ITriggerContext<EntityDb> context, CancellationToken cancellationToken) {
        if (context.Entity.Id != 0 && context.ChangeType != ChangeType.Added) {
            ...
            // Do stuff
            ...
        }
    }
}

Track database inserts with specific data

3 Answers3