CreateUniqueMethodOptions with temporal tables

[tschennie]

Hello,

we are using the CreateUniqueMethodOptions for testing. Now we added a temporal table and we are getting following error while executing the tests:

Microsoft.Data.SqlClient.SqlException: Failed to delete the XXX table because this is not a supported operation for temporal tables with system versioning.

If I delete the table on my own, the test works fine once. But then, I will get the error again...

Does this method supports temporal tables und if yes, how?

Thanks, Jenny

[JonPSmith]

Hi @tschennie,

The CreateUniqueMethodOptions only sets up the options for the DbContext so that shouldn't cause the type of exception. I expect its how you reset / create the database. The best thing would be to provide me with:

1. The code in the test
2. The Exception with its full stacktrace with details on which line throw the exception.
3. What version of .NET you are using and the version for EfCore.TestSupport.

[tschennie]

1.

    DbContextOptions<InvoiceDbContext>? options = this.CreateUniqueMethodOptions<InvoiceDbContext>();
    await using InvoiceDbContext dbContext = new(options);
    dbContext.Database.EnsureClean();

2. Message:  Microsoft.Data.SqlClient.SqlException: Failed to delete XXX.dbo.Invoices table because this is not a supported operation for temporal tables with system versioning.

SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite) SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName) SqlCommand.ExecuteNonQuery() RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject) MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection) <>c__21.b2_0(<>fAnonymousType0`2 s) <>cDisplayClass12_0`2.b0(DbContext c, TState s) SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func2 operation, Func2 verifySucceeded) ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func2 operation) ExecutionStrategyExtensions.Execute[TState](IExecutionStrategy strategy, TState state, Action1 operation) CleanDatabaseExtensions.EnsureClean(DatabaseFacade databaseFacade, Boolean setUpSchema) GetInvoicesHandlerTests.Handle_Admin_AllInvoices() Zeile 59 GetInvoicesHandlerTests.Handle_Admin_AllInvoices() Zeile 71 --- End of stack trace from previous location ---

3. We are using .NET 6.0 and EfCore.TestSupport 5.2.2

And this is the configuration for the temporal table:

    pBuilder.ToTable("Invoices",
        tableBuilder => tableBuilder.IsTemporal(temporalTableBuilder =>
            {
                temporalTableBuilder.UseHistoryTable("InvoicesHistory");
            }));

[JonPSmith]

The dbContext.Database.EnsureClean() method uses the SQL DROP command, which won't work on a temporal tables. You should use:

dbContext.Database.EnsureDeleted();
dbContext.Database.EnsureCreated();

This deleted the current database and then creates a new database. This is slower but will work with temporal tables.