I'm working on a project using .NET 4.8 and Entity Framework 6.1.

Until now we did mainly sync code but we would like to start using async. For this we will need to do async DB operations.

Though, I'm struggling with the transaction part

• EF 6.1 has async operation for queries: ToListAync(), SingleOrDefaultAsync(), SaveChangesAsync(), etc
• But not for transactions: there is only Database.BeginTransaction() and DbContextTransaction.Commit()

What should I do when transactions are needed ?

• Use only sync code (use SaveChanges, BeginTransaction(), and Commit()) ?
• Mix sync and async (use SaveChangesAsync(), BeginTransaction() and Commit()) ?

CodePudding user response：

Simply do all your changes and then use SaveChangesAsync() and all operations are committed in one transaction. On failure, an exception is thrown and all changes are ROLLED BACK. Usually you don't need to manually control transactions.

See here: https://docs.microsoft.com/en-us/ef/core/saving/transactions

Default transaction behavior

NOTE: Microsoft only talks about SaveChanges but the same applies for SaveChangesAsync.

Typical code:

using Microsoft.EntityFrameworkCore;

using (var ef = new DbCdr.Context(logDebugToConsole: true))
{
    ////ADD
    DbCdr.CCLog new1 = new DbCdr.CCLog()
    {
        Time = DateTime.Now,
        ConcurrentCalls = 99
    };
    // Begins tracking the given entity, and any other reachable entities that are not already being tracked, in the Added state such that they will be inserted into the database when SaveChanges() is called.
    ef.CCLogs.Add(new1);


    //// DELETE
    // actually queries the Db, thus use Async
    var toDel = await ef.Cdrs.Where(_ => _.ServerId == 1).FirstAsync();
    // Begins tracking the given entity in the Deleted state such that it will be removed from the database when SaveChanges() is called.
    ef.Cdrs.Remove(toDel);

    //// UPDATE
    ///// actually queries the Db, thus use Async
    var toUpdate = await ef.Cdrs.OrderByDescending(_ => _.StartTime).FirstAsync();
    toUpdate.ReleaseTime = DateTime.Now; // changes will be tracked and saved on SaveChanges()

    await ef.SaveChangesAsync(); // all will actually be done here in a transaction. Or if something fails throw Exception and ROLLBACK
}

The Remove() and Add() functions are not Asnyc since they only start the Tracking and don't execute any queries.

To get some insight how EF works you can enable logging in the context like so, that gives you a very detailed output of the executed queries and actions such as "Starting Transactions":

public readonly bool LogToConsole;
public Context(bool logDebugToConsole = false)
{
    LogToConsole = logDebugToConsole;
    ConnString = $"User ID=xxxxxx;Password=xxxxxx;Host=xxxxx;Port=xxxxx;Database=xxxxxx; Pooling = true;";
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseNpgsql(connectionString: ConnString);

        if (LogToConsole)
            optionsBuilder.LogTo(Console.WriteLine);
    }
}

Some more advices for newcomers to EntityFramework:

• the context is intendet to be shortlived, if needed use pooling too avoid reopen tcp connections all time. Never use a context prolonged.
• await ANY async function ALWAYS.