Efcore: Added a version of ExecuteSqlRaw etc that is executed within the SaveChanges transaction

[JonPSmith]

I have found a few times that I wanted to execute SQL update/delete commands inside a transaction in the SaveChanges/SaveChangesAsync. This is always about changing data across a collection, and its about performance.

At the moment my current example is from cascading a soft delete setting into dependences - my cascade soft delete solution is good but its makes a lot of discrete database accesses. So the examples are:

• Update the soft-delete column value of a collection - UPDATE [Review] SET SoftDelete = 1 WHERE BookId = 123
• Hard delete collection where SoftDelete is set - DELETE FROM [Review] WHERE BookId = 123 AND SoftDelete <> 0

Another example from a client situation where a price change had to be applied to the Price column in a series of large entity, which takes time to load. I can't use ExecuteSqlRaw because there are many updates and I need them done in a transaction.

Of course I can use transactions to do this, but a) its a lot of work and b) I couldn't create a CascadeSoftDelete library to do this in itself.

My idea

1. Have versions of ExecuteSqlRaw etc. methods which are run within the SaveChanges transaction, e.g. ExecuteSqlRawInSaveChanges and ExecuteSqlInterpolatedInSaveChanges.
2. There won't be Async version because the sync/async is defined by the SaveChanges/SaveChangesAsync call.
3. The commands should have a extra parameter to say whether the ExecuteSqlRawInSaveChanges etc are executed before or after the EF Core changes - default is after (as its safer).
4. If there are multiple ExecuteSqlRawInSaveChanges etc then they are grouped into two sets, before and after, and then executed in order the ExecuteSqlRawInSaveChanges were called.
5. In some cases it would be nice to return the int result for each ExecuteSqlRawInSaveChanges etc via an event, but that's not absolutely necessary as you could check via SQL that the command was run.

Here is an example
```c#
var event = context.Database.ExecuteSqlRawInSaveChanges(
"UPDATE [Review] SET SoftDelete = 1 WHERE BookId = {0}", 123);

## Possible 'pit of failure' issues

I have tried to look at the down sides of this feature (other than the work to do it 😄) to see if it could cause problems.

- Setting the `runAfter` in the `ExecuteSqlRawInSaveChanges` version is horrible because its not obvious its not in the params - e.g. in the example below the `false` is setting the `runAfter` parameter, but people would forget that!
```c#
var event = context.Database.ExecuteSqlRawInSaveChanges(
      "UPDATE [Review] SET SoftDelete = 1 WHERE BookId = {0}", false, 123);

• Here are some options to this problem
  
  
  
  • Only run at the end of the SaveChanges - safest.
  
  
  • Have a prefix extension called something like ExecuteBefore(), e.g. context.Database.ExecuteBefore().ExecuteSqlRawInSaveChanges(... etc.).
  
  
  • Define before/after in the method name, e.g. ExecuteSqlRawStartSaveChanges / ExecuteSqlRawEndSaveChanges
  
  
  • Only implement ExecuteSqlInterpolatedInSaveChanges because an bool runAfter = true parameter works.
  
  
• What happens if the ExecuteSqlRawInSaveChanges is run before EF Core's update and the SQL deletes something that the EF Core tries to update. Because the developer has specifically set the runAfter parameter to false, then I think they would know its their job to get that right.

• With SQL commands the order matters, which is different to how EF Core works, i.e. EF Core works the correct order to apply things. Will this confuse people? I doubt it because its only the developers who know about SQL will be using this feature, and they are used to applying SQL in the correct order.

• If you don't implement events to get the result back from ExecuteSqlRawInSaveChanges etc., then some people will say its not properly implemented.

Thanks for reading this.

[roji]

@JonPSmith is this purely in order to include your custom SQL inside the SaveChanges batch (rather than transaction)? In other words, is this about reducing a single round-trip to the database? Can you also provide more details on why you can't create a CascadeSoftDelete library with the current API, what exactly is blocking?

[JonPSmith]

I want the SQL to be run inside the transaction that SaveChanges uses, i.e. I want the SQL commands be applied with the EF Core database updates, and if any part of the updates (EF Core or user SQL) fails then everything is dropped. So, to answer your question, its mainly about the transaction but it should reduce round-trips in some cases (haven't designed the SQL version yet).

UPDATE: Ah, I think you are asking if the SQL could go out in SaveChanges's main database access - that would be a bonus!

I have worked on a CascadeSoftDelete service to see if I could emulate the SQL cascade process, but for soft delete. The parts are available in the repo that goes with my book. CascadeWalker is the main part, and it has to use explicit loading to load each collection, which I know produces lots of database round-trips and it could be a lot of data too. (here is a Unit Test in case it helps understand what I am trying to do.)

I haven't build a SQL version of my CascadeWalker - maybe I should do that to check that the improvements in performance should be there.

As I said, I have had other situations where 'SQL run in the same transaction and EF Core updates' would be useful. In the last case it was updating the Price in a hierarchical quote when something changes, like the number of items, in a quote part some steps down in the hierarchy.

I would also like you and team's input as to whether you think other people would use this. If its just me then its not worth it.

[roji]

@JonPSmith SaveChanges only starts a new transaction if one hasn't been started yet. So if the idea is just to run some arbitrary thing in the same transaction as what SaveChanges does, isn't it sufficient to just start a transaction on the context, and then run your raw SQL and SaveChanges within that? I mean something like the following:

c# await ctx.Database.BeginTransactionAsync(); await ctx.Database.ExecuteSqlRawAsync("Some Raw SQL"); ctx.Blogs.Add(new Blog()); await ctx.SaveChangesAsync(); ctx.Database.CommitTransaction();

Even though everything runs in the same transaction, the above still means two database roundtrips, but I'm not sure it makes sense to introduce a complex feature just to avoid that.

Cascading soft deletes does sound useful as a feature - it would be interesting to see a prototype for that.

[JonPSmith]

Hi @roji,

Thanks for considering this feature. I could use a transaction, but I don't like using transactions in a library because it might interfere with the user's use of transactions/retry code. However I don't want the EF Core team spend time on something that isn't likely to be used by other developers.

On the Cascading soft deletes feature I have a working prototype and I'm going to talk about it in my next "EF Core in-depth" series and see if people would find it useful. If the feedback is positive I will turn it into NuGet library. I'll add a link to the article in this issue when it comes out.

Thanks for your feedback, and I have closed this feature request.

[roji]

Thanks for considering this feature. I could use a transaction, but I don't like using transactions in a library because it might interfere with the user's use of transactions/retry code.

I'm not completely sure what you're looking for... But here's an idea. You can check if a transaction is already in progress (this is how SaveChanges does this internally). If there's no transaction in progress, then you can start one and commit it after the SaveChanges (this what I proposed above). If there already is a transaction, then you don't need to do anything - any SQL you run (raw or otherwise) will implicitly run in the same transaction as your SaveChanges.

The end result should be something that doesn't interfere with any user code, and is pretty much identical to what SaveChanges already does internally.

[JonPSmith]

Hi @roji,

Yes! I forgot that I can check if a transaction was in play or not. Brilliant! That makes making a SQL version possible.

In writing the article on Soft Delete/Cascade Soft Delete and building up code there are all sorts of interesting things to overcome. And then turning that into a library is another level up as you have to handle all sorts of things like property and field navigational properties, loading any entity via any arrangement of primary keys (can't use Find as you can't apply IgnoreQueryFilters to it) let alone building a SQL version too. Fun, but building a library will take quite a bit of time.

I need to see if people would use a Soft Delete/Cascade Soft Delete NuGet library first before I commit to that. But thanks pointing out my blind spot - let's see what happens.

[JonPSmith]

Hi @roji,

Just to say I have released an article EF Core In depth – Soft deleting data with Global Query Filters which describes the cascade soft delete service, with the software in https://github.com/JonPSmith/EfCore.SoftDeleteServices.

It's not to a level it can be released as a library, but I'm asking for feedback from the readers of the article as to whether they might find it useful. We will see if people would like it or not.

PS. I liked the addition of IsOnDependent and IsCollection to the INavigational etc. in EF Core 5 - very helpful.

[roji]

Great! Will do my best to take a look at it.