Improve workflow of Temporal Rollback

I wanted to write this issue because I struggled to find much help and advice on how to rollback data using Temporal tables with EFCore. On the face of it things seem straight forward enough and watching the videos I was keen to start using it. My main goal was to present a number of snapshots to the user and offer an option to rollback to a snapshot.

Depending on your data structure your millage will vary with trying to rollback data to a timestamp and I wanted to detail them here in the hope it may prompt either changes to EFCore or an article with best practice.

The issues I stumbled across: where:

- TemporalAsOf does not support NoTrackingWithIdentityResolution
- row version conflict when restoring Temporal data.
- unable to insert identity

**TemporalAsOf does not support NoTrackingWithIdentityResolution**

This will only be an issue if your data structure happens to reference the same object in multiple places. When doing a normal query with tracking on you only need to include one instance and all the other instances will be populated. However if you then want to use TemporalAsOf you are not able to get the full data set back as it does not track.

The solution is to create your own TemporalAsOf helper that allows you to specify the QueryTrackingBehaviour. This is detailed in issue [https://github.com/dotnet/efcore/issues/27289](url)

```
///
/// Applies temporal 'AsOf' operation on the given DbSet, which only returns elements that were present in the database at a given
/// point in time.
///
///
///
/// Temporal information is stored in UTC format on the database, so any arguments in local time may lead to
/// unexpected results.
///
///
/// The default tracking behavior for queries can be controlled by .
///
///
/// See Using SQL Server temporal tables with EF Core
/// for more information and examples.
///
///
/// Source DbSet on which the temporal operation is applied.
/// representing a point in time for which the results should be returned.
/// An representing the entities at a given point in time.
public static IQueryable TemporalAsOf(
this DbSet source,
DateTime utcPointInTime,
QueryTrackingBehavior queryTrackingBehavior
)
where TEntity : class
{
#pragma warning disable EF1001 // Internal EF Core API usage.
var queryableSource = (IQueryable)source;
var queryRootExpression = (QueryRootExpression)queryableSource.Expression;
var entityType = queryRootExpression.EntityType;

var query = queryableSource.Provider.CreateQuery(
new TemporalAsOfQueryRootExpression(
queryRootExpression.QueryProvider!,
entityType,
utcPointInTime)).AsTracking(queryTrackingBehavior);
return query;
#pragma warning restore EF1001 // Internal EF Core API usage.
}`
```

**Row version conflict when restoring Temporal data**

If you are using Timestamps in your database then you will need to apply the timestamps from your current versions to any temporal version before you can save it . This can get quite complicated with a deep nested structure.

My fix was to get both the temporal version and current version and copy across the timestamps before saving. However it feels like allot of work that could be avoided with temporarily bypassing the row versioning logic.

**unable to insert identity**

If you are not using GUIDs for your primary key then when you go to rollback a delete it will require you to insert the ID for the item. This is blocked by default and requires you to turn on IDENTITY_INSERT which can be done using raw SQL. The problem is that you can only do this on a per table basis and when restoring rows in multiple tables you need to turn it on and off on a per table basis.

This was by far the most complicated issue to solve since I needed to batch all the updates to maintain the integrity of the database but SaveChanges only allowed me to turn INSERT_IDENTITY on for a single table. I eventually found a solution that overrides the EF Insert behaviour to always turn INSERT_IDENTITY on.

```
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.EntityFrameworkCore.SqlServer.Update.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.Update;

///
/// SqlServerUpdateSqlGenerator with Insert_Identity.
///
public class SqlServerUpdateSqlGeneratorInsertIdentity : SqlServerUpdateSqlGenerator
{
///
/// Initializes a new instance of the class.
///
/// The dependencies.
public SqlServerUpdateSqlGeneratorInsertIdentity(UpdateSqlGeneratorDependencies dependencies)
: base(dependencies)
{
}

public static bool IdentityInsert { get; set; } = false;

public override ResultSetMapping AppendBulkInsertOperation(StringBuilder commandStringBuilder, IReadOnlyList modificationCommands, int commandPosition, out bool resultsContainPositionMapping, out bool requiresTransaction)
{
if(IdentityInsert)
{
var columns = modificationCommands[0].ColumnModifications.Where(o => o.IsWrite).Select(o => o.ColumnName).ToList();
var schema = modificationCommands[0].Schema;
var table = modificationCommands[0].TableName;

GenerateIdentityInsert(commandStringBuilder, table, schema, columns, on: true);
var result = base.AppendBulkInsertOperation(commandStringBuilder, modificationCommands, commandPosition, out resultsContainPositionMapping, out requiresTransaction);
GenerateIdentityInsert(commandStringBuilder, table, schema, columns, on: false);

return result;
}
else
{
return base.AppendBulkInsertOperation(commandStringBuilder, modificationCommands, commandPosition,out resultsContainPositionMapping, out requiresTransaction);
}
}

private void GenerateIdentityInsert(
StringBuilder builder,
string table,
string schema,
IEnumerable columns,
bool on)
{
var stringTypeMapping = Dependencies.TypeMappingSource.GetMapping(typeof(string));

builder.Append("IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE").Append(" [name] IN (")
.Append(string.Join(", ", columns.Select(stringTypeMapping.GenerateSqlLiteral)))
.Append(") AND [object_id] = OBJECT_ID(").Append(
stringTypeMapping.GenerateSqlLiteral(
Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema))).AppendLine("))");

builder.Append("SET IDENTITY_INSERT ")
.Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema)).Append(on ? " ON" : " OFF")
.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
}
}
```

In the DBContext you need to add.

`optionsBuilder.ReplaceService();`

Finally I added a helper to the DbContext so that we where not always saving with IDENTITY_INSERT on.

```
public async Task SaveChangesWithIdentityInsertAsync()
{
try
{
SqlServerUpdateSqlGeneratorInsertIdentity.IdentityInsert = true;
await SaveChangesAsync();
}
finally
{
SqlServerUpdateSqlGeneratorInsertIdentity.IdentityInsert = false;
}
}
```

Its really horrible as it uses the static property but so far its the best I could come up with.

I wrapped all this into a helper method called TemporalRollback.

```
public static DbContext GetDbContext(this DbSet dbSet) where T : class
{
var infrastructure = dbSet as IInfrastructure;
var serviceProvider = infrastructure.Instance;
var currentDbContext = serviceProvider.GetService(typeof(ICurrentDbContext))
as ICurrentDbContext;
return currentDbContext.Context;
}

public static async Task TemporalRollback(this DbSet source, Expression> where,DateTime utcPointInTime) where TEntity : class,ITrackableObject
{
var context = source.GetDbContext();

var rollbackVersion = source.TemporalAsOf(utcPointInTime).Where(where).ToList();
var currentVersion = source.Where(where).ToList();

foreach (var update in rollbackVersion.Join(currentVersion,a=>a.Id, b=>b.Id,(a,b)=> new { rollback = a,current = b}))
{
context.Entry(update.current).CurrentValues.SetValues(update.rollback);
}

//Delete any orphaned quantities
var entitiesToDelete = currentVersion.Except(rollbackVersion, new RollbackComparer());
source.RemoveRange(entitiesToDelete);

var entitiesToAdd = rollbackVersion.Except(currentVersion, new RollbackComparer());
await source.AddRangeAsync(entitiesToAdd);
}

private class RollbackComparer : IEqualityComparer where TEntity : class, ITrackableObject
{
public bool Equals(TEntity x, TEntity y)
{
return x.Id == y.Id;
}

public int GetHashCode([DisallowNull] TEntity obj)
{
return obj.Id.GetHashCode();
}
}
```