I want to do the following:
If the insert fails, nothing will be done and everything is rollbacked. The problem is I don't have a good way to rollback the API request if the committing of the transaction fails because it is not a database operation. The only thing I can think of is delete the data from the endpoint. Is there a better way? I am using entity framework. Thanks.
So if I have understood correctly, you only want to commit to the DB if the API call was successful, but can't call the API until you have the DB generated primary Key from the DB insert.
So this would lead to a kind of 2 phase commit type of approach, but what happens if the insert is successful, the API call is successful, but the DB commit fails? (if happens!)
Therefore, I think that you should consider to instead implement the Saga design Pattern.
This is design for complex distributed systems, especially using microservices, where you need to ensure data consistency across different distributed systems. Basically you build a kind of chain of transactions which need to be executed in order. You then go through them one by one and if successful then you move onto the next.
But, for every transaction you also have a compensating transaction. So that if a transaction fails then you work backwards through your list executing all the compensating transactions one by one until in the end you have the same initial state.
There are different ways of doing this such as the Choreography-based saga or the Orchestration-based saga. You can find more details and a better explanation here: https://microservices.io/patterns/data/saga.html