How to execute manual SQL as part of SaveChanges call in Entity Framework Core?

Question

I am looking for a way to add manual raw SQL call to standard AppDbContext interaction. Say I have following code:

Message m = new Message
{
    Title = message.Title,
    Body = message.Body,
    Date = DateTime.Now,
    Foo = "foo"
};

_context.Messages.Add(m);
// Add some manual UPDATE SQL here, like:
// UPDATE SomeOtherTable SET Foo = 'Something' WHERE SomeComplex = 'Condition'

_context.SaveChanges();

The most important thing is to make that manual SQL part of command / transaction that gets created and executed as part of SaveChanges. If there is a way to inject my modification into SQL generated by SaveChanges - that's what I'm looking for.

Just wrap update query and SaveChanges within transaction. context.Database.BeginTransaction() and then transaction.Commit() — Fabio
– Fabio, Commented Apr 24, 2020 at 5:06
So every time you save any message you want to update some property of all messages in the table? — Caius Jard
– Caius Jard, Commented Apr 24, 2020 at 5:19

E-Bat · Accepted Answer · 2020-04-24 05:35:05Z

2

I use an overload for SaveChangesAsync on my DbContext base that accepts any arbitrary raw sql, the key is to wrap all the processing within a manually created transaction:

  public async Task<int> SaveChangesAsync(string customCommand,
        CancellationToken cancellationToken = default(CancellationToken),
        params object[] parameters)
    {
        using (var transaction = Database.BeginTransaction())
        {
            try
            {
                var result = await base.SaveChangesAsync(true, cancellationToken);

                await Database.ExecuteSqlCommandAsync(customCommand, parameters);
                transaction.Commit();

                return result;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw ex.Handle();
            }
        }
    }

Note: Handle() is an extension method where I transform the t-sql exceptions into my business exceptions, so that method is not relevant here.

edited Apr 24, 2020 at 5:35

answered Apr 24, 2020 at 5:17

E-Bat

4,9121 gold badge36 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fabio Over a year ago

I am afraid that you must call transaction.Commit, but rollback will be executed automatically as part of transaction disposing if some of the queries fails.

johnw182 Over a year ago

This wraps everything in one batch locally but will still result in two trips to the server. No?

Collectives™ on Stack Overflow

How to execute manual SQL as part of SaveChanges call in Entity Framework Core?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related