How can I get EF Core 8.0 / ASP.NET Core 8.0 Web API to save to a table that has an insert trigger?

I have an EF Core 8 & ASP.NET Core 8 Web API that is supposed to save posts request received from an endpoint into a database table.

The table has a trigger that fires after an insert and executes a stored procedure that updates a record in another table only if the insert meets a condition in the trigger.

I originally was getting the following error when the API tries to save to the table:

Microsoft.EntityFrameworkCore.DbUpdateException: 'Could not save changes because the target table has database triggers. SqlException: The target table 'tablename' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

This stopped after I updated the model code to inform EF Core that the table has a trigger.

Now I get the error message:

Microsoft.EntityFrameworkCore.Update[10000] An exception occurred in the database while saving changes for context type 'Context_name'. Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Int64'.
at Microsoft.Data.SqlClient.SqlBuffer.get_Int64()
at Microsoft.Data.SqlClient.SqlDataReader.GetInt64(Int32 i)
at lambda_method348(Closure, DbDataReader, Int32)
at Microsoft.EntityFrameworkCore.RelationalPropertyExtensions.GetReaderFieldValue(IProperty property, RelationalDataReader relationalReader, Int32 ordinal, Boolean detailedErrorsEnabled)
at Microsoft.EntityFrameworkCore.Update.ModificationCommand.PropagateResults(RelationalDataReader relationalReader)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetAsync(Int32 startCommandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetAsync(Int32 startCommandIndex, RelationalDataReader reader, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeAsync(RelationalDataReader reader, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore

This error happens only if the condition in the trigger for executing the stored procedure is met.

There is no column in the table that has an Int64 data type except the id column that is automatically generated. I can't seem to figure out what is causing it to throw up the conversion error.

When I insert into the table from SSMS everything works fine.

The API is able to save to the table only When the condition for the trigger to execute the stored procedure that updates the other table is not met by the insert, or I disable the trigger.

Here is a snapshot of the structure of the table the API inserts into

And this is a snapshot of the structure of the table the trigger updates a record that matches the insert using a stored prod.

Like I said before, in execution, the stored procedure also inserts records into an additional table or two depending on conditions.

The error was caused by a return statement in the trigger code. Once I removed this, the error ceased and everything worked as expected.

Thanks to points to possible causes from a Michael Taylor from Microsoft Q&A