arch / autohistory Goto Github PK

Ef Core shows the AutoHistory require to have 'rowId' not follow the updated entity's Id, but the 'last_insert_rowid()', what does it mean?

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[@p0='?' (Size = 56), @p1='?' (DbType = DateTime), @p2='?' (DbType = Int32), @p3='?' (Size = 1), @p4='?' (Size = 11)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "AutoHistory" ("Changed", "Created", "Kind", "RowId", "TableName")
      VALUES (@p0, @p1, @p2, @p3, @p4);
      SELECT "Id"
      FROM "AutoHistory"
      WHERE changes() = 1 AND "rowid" = last_insert_rowid();

The result is that, 'RowId' was forced to be the same as 'Id' in AutoHistory. Or it will throw Concurrency Exception.
So the AutoHistory looks like this, all records happened to have same 'Id' and 'RowId' will insert with success, others fail with exception.

Wondering that if the 'PrimaryKey' produced the 'RowId' with certain restrictions?

private static string PrimaryKey(this EntityEntry entry)
        {
            var key = entry.Metadata.FindPrimaryKey();

            var values = new List<object>();
            foreach (var property in key.Properties)
            {
                var value = entry.Property(property.Name).CurrentValue;
                if (value != null)
                {
                    values.Add(value);
                }
            }

            return string.Join(",", values);
        }

When the entity is Modified or Deleted, we can set the RowId to the primary key, when the entity is Added, however, we should have a way to set the PrimaryKey to RowId.

more details, see DbContextExtensions

Latest version 5.0.7 does not record delete operation.

Hi,

I'm currently using Arch/UnitOfWork and I need to integerate it with Arch/AutoHistory.

can u pls help?

Are there any plans for a net7 version of this?

In all of our projects we use:

• net7.0
• EfCore version 7.x.x

Questions
1.) Any plans to have a net7 verison, so users can pick and choose their version? Or any plans to upgrade to net7 as the EOL of net6 is rapidly approaching?

2.) Will using this in a net7 project that uses EfCore version 7.x.x cause issues?

Thanks

Turkish-specific expressions are not saved in the changed data line. There will probably be problems in other languages ​​as well.
e.g; ö,ü,ş,ı etc.

rename parameter createHistoryCallback to autoHistoryFactory or simething for more meaningful

In Unit Of Work Pattern (with repository pattern) , I am using dbContext.SaveChangesAsync() in repository wrapper (for all DB changes to be called at one place only). How can I configure auto history only for few entities, not all. I do not need history of all entities(tables).

When new a entity, I Use DbSet().Add(entity) Then context.Savechanges(); It can't record in autohistory table. But update or delete operation works well! My Microsoft.EntityFrameworkCore.AutoHistory version is 3.1.1. Looking forward to your suggestions

I have updated my project to .Net core 3.0.

Following the article, I did the below in my MVC web application project

class CustomAutoHistory : AutoHistory
{
    public int UserId { get; set; }
}

Then register it in the db context like follows:
modelBuilder.EnableAutoHistory<CustomAutoHistory>(o => { });

Then in my MVC controller code I did below

  if (ModelState.IsValid)
            {
                try
                {
                    _context.EnsureAutoHistory(() => new CustomAutoHistory()
                    {
                        UserId = Convert.ToInt16( User.FindFirstValue(ClaimTypes.NameIdentifier))
                     });
                    _context.Update(cust);
                    await _context.SaveChangesAsync();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!ClientExists(cust.Id))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
            }

CustomAutoHistory table script

CREATE TABLE [dbo].[|CustomAutoHistory](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RowId] nvarchar NOT NULL,
[TableName] nvarchar NOT NULL,
[Changed] nvarchar NULL,
[Kind] [int] NOT NULL,
[Created] datetime2 NOT NULL,
[UserId] [int] NULL,
CONSTRAINT [PK_CustomAutoHistory] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The customer records gets updated but there is no record in the Custom History table. Can you please check the custom AutoHistory?

Moin :)

I do have the question on how I do access the history entries? Are there methods available on the object, like:

Person person = _context.person.Find(1);
person.History()

Or are there other ways to access the history entries for a database row?

Thanks :)

AutoHistory model is market like internal,

I have no way to re read auto history records

I would like to use AutoHistory in my project where I need to query some history data with joining current data. Let's say I have an Order table

public class Order
{
    public int Id { get; set; }
    public OrderState OrderState { get; set; }
    public int ProductId { get; set; }
    public int UserId { get; set; }

    public DateTime CreatedOn { get; set; }
    public DateTime ModifiedOn { get; set; }
}
public enum OrderState
{
    Set=1,
    OnTheWay=2,
    Canceled=3
}

And I have an order record that is OnTheWay state. How can I query that what was the ModifiedOn column value before the state changed to OnTheWay from Set. This is just a simple case, we may have a lots of conditions depends on other columns, do you think it is costly to make use of this AutoHistory table like that?

Why is the EntityState.Added not supported? The case for the EntityState.Added is here.

But the filter on Line 33 filters for modified and deleted

Or is there a reason why this is missing?

There's any way to don't generate history for a specific entity?

I am using PostgreSQL and i am having this issue that my before and after values is being the same values saved.

Here's a similar issue from Arch/UnitOfWork
Arch/UnitOfWork#83

If you use a combined pk then there is an issue that you can not delete those rows. you can create it and they will be no record in history table. But a remove returns in an error. Tested with mssql.

Hi. I'm having trouble getting this started in my project. My project could save prior to adding AutoHistory. Now I get the below exception on any save. There still is no table named AutoHistory in the database.
Microsoft.EntityFrameworkCore 5.0.15
Microsoft.EntityFrameworkCore.AutoHistory 5.0.8

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid object name 'AutoHistory'.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) ClientConnectionId:ed0320bd-4a8f-40a6-9f46-2d7f56cab67b Error Number:208,State:1,Class:16 --- End of inner exception stack trace --- at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable1 commandBatches, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList1 entries) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList1 entriesToSave)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
at myDC.SaveChangesAsync(CancellationToken cancellationToken)

Could it be possible to have the latest released for net core 3?

Hi,

The AutoHistory table is created in the database but there are no records.

My config is:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // enable auto history functionality, 22023 is max limit supported by PostgreSQL
    modelBuilder.EnableAutoHistory(22023);
}

and during any save or update I am using the following:

await UnitOfWork.SaveChangesAsync(true);

Further technical details

EF Core version: 2.0.1
Database Provider: Npgsql.EntityFrameworkCore.PostgreSQL
Operating system: Windows 10
IDE: Visual Studio Enterprise 2017 15.4.5

It is more correct DateTime.Now than DateTime.UtcNow for Created property in the AutoHistory class. Because utcnow does not change.

I want to have two tables named Entity and EntityHistory created automatically after creating a Migration, with EntityHistory being completely generated automatically.

please help me!

https://github.com/Arch/AutoHistory I found this Library, but it creates a History for all DBSets. I want to have a separate History for each Entity.

I tried to enable recording of added entities as described in the README. Unfortunately it's not working.
In the code of AutoHistory there is an extension method called AddedHistory which seems to do the trick but this method is not called at all in the class DbContextExtensions

EF Core version: 5.0.11
AutoHistory version: 5.0.8

Introduced by https://github.com/Arch/AutoHistory/pull/61/files

When are you planning to add feature of insertion history, as currently it supports only updation and deletions

When upgrading to dotnet core 3.1

IdentityDbContext<User, Persona, string>' does not contain a definition for 'EnsureAutoHistory'

How to resolve this issue.

public class ProjectDbContext : IdentityDbContext<User, Persona, string> {
}

Could you get a new nuget package published with the support for EF Core 3 that got merged in?

I have a root object that has a number of child entities being created and modified from the root object.
Auto history seems to track only the changes from base entity. is there a way to track the child entities automatically?

I have install this nuget packet in my sample project. But this don't create any historie data in my database.
Here my implementation:

DbContext:

public class BlogDataDbContext: DbContext
{
        public BlogDataDbContext(DbContextOptions<BlogDataDbContext> options): base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.EnableAutoHistory(null);
        }

        

        public DbSet<Blog> Blogs { get; set; }
}

Somemethod of saving:

 public class EditModel : PageModel
    {
        private readonly BlogDataDbContext _context;

        public EditModel(BlogDataDbContext context)
        {
            _context = context;
            _context.EnsureAutoHistory();
        }

        [BindProperty]
        public Data.BlogData.Blog Blog { get; set; }

        public async Task<IActionResult> OnGetAsync(Guid? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            Blog = await _context.Blogs.SingleOrDefaultAsync(m => m.BlogId == id);

            if (Blog == null)
            {
                return NotFound();
            }
            return Page();
        }

        public async Task<IActionResult> OnPostAsync()
        {
            if (!ModelState.IsValid)
            {
                return Page();
            }

            _context.Attach(Blog).State = EntityState.Modified;

            try
            {
                await _context.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!BlogExists(Blog.BlogId))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return RedirectToPage("./Index");
        }

        private bool BlogExists(Guid id)
        {
            return _context.Blogs.Any(e => e.BlogId == id);
        }
    }

After the change of an element no new entry in the AutoHistorie table. This is strange...

The PK column in the history table is currently set to be an int with no straight forward method for providing an alternate type. In large scale systems this presents a hard cap on the number of history entries that can be created at a bit more than 2 billion. This is a large but not outrageously large value given that the table will log all changes written to the database. In addition most databases value uniqueness and speed over density when assigning int pk values so the actual number of records written can be significantly lower than ~2 billion. In practive in some of our high usage systems we are having to roll the audit table every couple of months.

We working on a work around that adds a value converter to the id property to "convert" the int ud to Guid.NewGuid(). This works to some degree but means we need to define a seperate model class to treat the audit log as a view if we want to retieve any data from it.

I think changing the type outright would probably present a major breaking problem for a number of applications but I think providing an option to inherit from a generic type would allow those that need it to define their own PK.

Hi

I am getting Auto History Result is as follows.

{
"before": {
"Addr1": "string",
"Addr2": "string",
"CntPerson": "string",
"CntPersonPhone": "string",
"CreatedBy": "Easwar",
"CreatedDate": "2018-01-27T13:50:37.2754446",
"EMail": "string",
"GstNumber": "string",
"IndiaState": "string",
"IsActive": true,
"MfrName": "Abott International",
"MfrShortName": "string",
"PanNumber": "string",
"Phone": "string",
"PinCode": "string",
"Place": "string",
"TinNumber": "string",
"TransactionTime": "0001-01-01T00:00:00",
"UpdatedBy": "Easwar",
"UpdatedDate": "2018-01-27T13:50:37.2754446"
},
"after": {
"Addr1": "string",
"Addr2": "string",
"CntPerson": "string",
"CntPersonPhone": "string",
"CreatedBy": "Easwar",
"CreatedDate": "2018-01-27T13:50:37.2754446",
"EMail": "string",
"GstNumber": "string",
"IndiaState": "string",
"IsActive": true,
"MfrName": "Abott International",
"MfrShortName": "string",
"PanNumber": "string",
"Phone": "string",
"PinCode": "string",
"Place": "string",
"TinNumber": "string",
"TransactionTime": "0001-01-01T00:00:00",
"UpdatedBy": "Easwar",
"UpdatedDate": "2018-01-27T13:50:37.2754446"
}
}

It Seems Before and After object are Same. Any Issue?

When recording history, there are fields that I don't want their real data recorded in history. These are like Personal Identifying data, or passwords (even after being hashed).

Though making this an extension method that would work in the OnModelCreating call, it would also be nice to see it as an attribute for the model fields themselves.

[ExcludeFromHistory(ReplacementValue="")]

HI, the latest update did not work with dotnet standard 2.0 but entity framework 3.1 work with .net standard 2.0

I am struggling to make AutoHistory work after chaning my controllers to async/await pattern.

I can't tell for sure it's that, but looking through my commits that is the only thing I can identify has changed since the time it worked.
Problem is that now every entry created by the framework is of the Kind Detached and the Changed column is NULL

Has anyone experienced this behaviour?
Thanks & kind regards,
Dino

Hi Team,
The value of before and after not save data with nested table, how can I log all data change include (table relationship change)

Thanks

Hello,

Is there any reason for having the Changed column just 2048 long?

Couldn't be replaced by nvarchar(max)?

Is it possible to add ApplicationUserId column (to know who make a changes) to audit table?

Hi,

I'm using EF Core but I'm not using migrations and context.EnsureAutoHistory() doesn't work; because the table doesn't exist. I want to create the table in SQL, can you tell me what fields does the AutoHistory table have?

Thank you very much in advance.

Hi, AutoHistory doesn't work with UnitOfWork if atrribute is nullable.

I rewrited your UnitTest.

    public void Entity_Update_AutoHistory_Test()
    {
        var unitOfWork = new UnitOfWork<BloggingContext>(new BloggingContext());

        var blogRepository = unitOfWork.GetRepository<Blog>();
        var postRepository = unitOfWork.GetRepository<Post>();

        var blog = new Blog()
        {
            BlogId = 1,
            Url = "htttp://www.google.com/"
        };

        var post = new Post()
        {
            PostId = 1,                
            Content = "Text 1",
            Title = "Text 2"
        };

        blogRepository.Insert(blog);
        postRepository.Insert(post);

        unitOfWork.SaveChanges(true);

        post.Age = 1;           

        unitOfWork.SaveChanges(true);
    }

...
public int? Age { get; set; }
...

It throws exception

We can use the modify history to recovery/rollback the changing.

Begin to support ef core 2.0

Is this repo still maintained? I will get a PR in for this if it is.

When trying to run it with .net 6 and ef core 6.0.1 this throws an exception during startup.

System.MissingMethodException: Method not found: 'System.Type Microsoft.EntityFrameworkCore.Metadata.ITypeBase.get_ClrType()'.
   at Microsoft.EntityFrameworkCore.DbContextExtensions.AutoHistory[TAutoHistory](EntityEntry entry, Func`1 createHistoryFactory)
   at Microsoft.EntityFrameworkCore.DbContextExtensions.EnsureAutoHistory[TAutoHistory](DbContext context, Func`1 createHistoryFactory)
   at Microsoft.EntityFrameworkCore.DbContextExtensions.EnsureAutoHistory(DbContext context)