Clever Geek Handbook

Track database inserts with specific data

I am maintaining an ASP.NET MVC application that talks to an MS SQL database through the Entity Framework.

For some time now we have seen cases where entities are updated or inserted into the database with a specific null field.

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

The problem is I have no idea where I should be doing this.

  • The object is inserted and updated in many places in the code;
  • I can override the Insert and Update methods of my child DBSet, but I will not catch changes caused indirectly (for example, by fetching an object from the database, modifying it, and calling it SaveChanges()

    ).

Where can I put code that will be able to access all database record changes on a specific object?

+3


source to share


2 answers


I believe you are looking for a property of ChangeTracking

yours DbContext

.

I use a similar method to create history for entities by capturing what has been changed / added / removed.

To do this, you can override the method within your DbContext SaveChanges()

and then intercept the records that are changing.



Make sure to call base.SaveChanges();

at the end of your override to actually save the changes.

Here, for example, let's 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();    
    }

}
+4


source


I think the Audit.NET library with EntityFramework Extension can help you with this requirement because you need minimal changes to your code.

For example, let's say you have the following context:

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

And you want to audit the changes when the Date

table field Posts

should 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 launch 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

In this configuration, it will generate json files on your filesystem with contents 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
  }
}
0


source






More articles:


All Articles