Audit trail with Entity Framework Core

Question

I have an ASP.NET core 2.0 using Entity Framework core on a SQL Server db.

I have to trace and audit all the stuff made by the users on the data. My goal is to have an automatic mechanism writing all what is happening.

For example, if I have the table Animals, I want a parallele table "Audit_animals" where you can find all the info about the data, the operation type (add, delete, edit) and the user who made this.

I already made this time ago in Django + MySQL, but now the environment is different. I found this and it seems interesting, but I'd like to know if there are better ways and which is the best approach to do this in EF Core.

UPDATE

I'm trying this and something happens, but I have some problems.

I added this:

1. services.AddMvc().AddJsonOptions(options => {
   
               options.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
           }); 
   

2. public Mydb_Context(DbContextOptions<isMultiPayOnLine_Context> options) : base(options)
   {
       Audit.EntityFramework.Configuration.Setup()
           .ForContext<Mydb_Context>(config => config
               .IncludeEntityObjects()
               .AuditEventType("Mydb_Context:Mydb"))
           .UseOptOut()
   }
   

3. public MyRepository(Mydb_Context context)
   {
       _context = context;
       _context.AddAuditCustomField("UserName", "pippo");
   
   }
   

I also created a table to insert the audits (only one to test this tool), but the only thing I got is what you see in the image. A list of json files with the data I created.... why??

Settles

Have you looked up SQL Server's Change Data Capture feature? – Douty

It might also be worth looking at Temporal Tables if you are using SQL Server 2016 - Note that temporal tables are not a replacement for the change data capture (CDC) feature. CDC uses the transaction log to find the changes and typically those changes are kept for a short period of time (depending on your ETL timeframe). Temporal tables store the actual changes in the history table and they are intended to stay there for a much longer time. – Barhorst

@RossBush Ok, but I don't this SQL Server's Change Data Capture can track the user logged in my ASP.NET core service, right? – Settles

@CalC Also this solution gives me some doubts. How can it trace the logger user? – Settles

Sounds like you need to do the auditing at the application level rather than db. Maybe subclass your dbContext and override the savechanges method. – Hebrew

Your question is too broad. No one here can tell you what to do. It's up to you to determine what potential solution is best for your application based on the requirements. Stack Overflow is not the place for crowd-sourcing opinions or favorite libraries. – Promycelium

@ChrisPratt look the update – Settles

Answer 1

Read the documentation:

Event Output

To configure the output persistence mechanism please see Configuration and Data Providers sections.

Then, in the documentation on Configuration:

If you don't specify a Data Provider, a default FileDataProvider will be used to write the events as .json files into the current working directory. (emphasis mine)

Long and short, follow the documentation to configure the data provider you'd like to use.

Promycelium

Good, but it doens't specify how I have to create this MyCustomDataProvider – Settles

Or, at least, there is an example, but I can find only an example to work with file – Settles

Ok ok, I almost got it. It now saves the json data in a table in my db, but it crashes when I want to save my real data... I use the primay key with auto-increment, so I don't set the ID value. When I call the SaveChangesAsync method, I get "Unable to track an entity of type 'DynamicIdModel' because primary key property 'Id' is null." Do you know how to avoid this?? – Settles

@PieroAlberto which data provider are you using? feel free to open a new issue on github – Cubby

@Cubby Hi pirat, probably I made some mistakes while using it for the first time. Editing the code I fixed this problem and know works like charms. Thanks for the tool, is very useful ;) – Settles

Anyway, I'm using SQL Server and I installed Audit.NET.SqlServer and Audit.EntityFramework – Settles

Answer 2

If you are going to map the audit table (Audit_Animals) to the same EF context as the audited Animals table, you can use the EntityFramework Data Provider included on the same Audit.EntityFramework library.

Check the documentation here:

Entity Framework Data Provider

If you plan to store the audit logs in the same database as the audited entities, you can use the EntityFrameworkDataProvider. Use this if you plan to store the audit trails for each entity type in a table with similar structure.

There is another library that can audit EF contexts in a similar way, take a look: zzzprojects/EntityFramework-Plus.

Cannot recommend one over the other since they provide different features (and I'm the owner of the audit.net library).

Cubby

Answer 3

Update:

.NET 6 and Entity Framework Core 6.0 supports SQL Server temporal tables out of the box.

See this answer for examples:

https://mcmap.net/q/139110/-net-core-entity-framework-and-sql-server-temporal-tables-automatic-scaffolding

Original:

You could have a look at Temporal tables (system-versioned temporal tables) if you are using SQL Server 2016< or Azure SQL.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

From documentation:

Database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011.

There is currently an open issue to support this out of the box:

https://github.com/dotnet/efcore/issues/4693

There are third party options available today but since they are not from Microsoft it is of course a risk that they won't be supported in future versions.

https://github.com/Adam-Langley/efcore-temporal-query

https://github.com/findulov/EntityFrameworkCore.TemporalTables

I solved it like this:

If you use the included Visual Studio 2019 LocalDB (Microsoft SQL Server 2016 (13.1.4001.0 LocalDB) you will need to upgrade if you use cascading DELETE or UPDATE. This is because Temporal tables with cascading actions is not supported in that version.

Complete guide for upgrading here:

https://mcmap.net/q/139172/-how-do-i-upgrade-sql-server-localdb-to-a-newer-version

Start by adding a new empty migration. I prefer to use Package Manager Console (PMC):

Add-Migration "Temporal tables"

Should look like this:

public partial class Temporaltables : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {

    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

Then edit the migration like this:

public partial class Temporaltables : Migration
{
    List<string> tablesToUpdate = new List<string>
        {
           "Images",
           "Languages",
           "Questions",
           "Texts",
           "Medias",
        };

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql($"CREATE SCHEMA History");
        foreach (var table in tablesToUpdate)
        {
            string alterStatement = $@"ALTER TABLE [{table}] ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN
     CONSTRAINT DF_{table}_SysStart DEFAULT GETDATE(), SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN
     CONSTRAINT DF_{table}_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'),
     PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{table}] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.[{table}]));";
            migrationBuilder.Sql(alterStatement);
        }
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        foreach (var table in tablesToUpdate)
        {
            string alterStatement = $@"ALTER TABLE [{table}] SET (SYSTEM_VERSIONING = OFF);";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{table}] DROP PERIOD FOR SYSTEM_TIME";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{table}] DROP DF_{table}_SysStart, DF_{table}_SysEnd";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{table}] DROP COLUMN SysStartTime, COLUMN SysEndTime";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"DROP TABLE History.[{table}]";
            migrationBuilder.Sql(alterStatement);
        }
        migrationBuilder.Sql($"DROP SCHEMA History");
    }
}

tablesToUpdate should contain every table you need history for.

Then run Update-Database command.

Original source, a bit modified with escaping tables with square brackets etc:

https://intellitect.com/updating-sql-database-use-temporal-tables-entity-framework-migration/

Testing Create, Update and Delete will then show a complete history.

[HttpGet]
public async Task<ActionResult<string>> Test()
{
    var identifier1 = "OATestar123";

    var identifier2 = "OATestar12345";

    var newQuestion = new Question()
    {
        Identifier = identifier1
    };
    _dbContext.Questions.Add(newQuestion);
    await _dbContext.SaveChangesAsync();

    var question = await _dbContext.Questions.FirstOrDefaultAsync(x => x.Identifier == identifier1);
    question.Identifier = identifier2;
    await _dbContext.SaveChangesAsync();

    question = await _dbContext.Questions.FirstOrDefaultAsync(x => x.Identifier == identifier2);
    _dbContext.Entry(question).State = EntityState.Deleted;
    await _dbContext.SaveChangesAsync();

    return Ok();
}

Tested a few times but the log will look like this:

This solution has a huge advantage IMAO that it is not Object Relational Mapper (ORM) specific and you will even get history if you write plain SQL.

The History tables are also read only by default so less chance of a corrupt audit trail. Error received: Cannot update rows in a temporal history table ''

If you need access to the data you can use your preferred ORM to fetch it or audit via SQL.

Fend