EF Core set XACT_ABORT for all database connections

Question

I am using EF Core 3.1 to connect to SQL Server. I would like to SET XACT_ABORT ON for SQL Server on for all the connections I make in my application.

Is there a hook on start up or context creation that I can run this? Because I am working with AWS RDS, I do not have the ability to turn it on server wide.

Any help would be appreciated.

Answer 1

Open the SqlConnection and set XACT_ABORT ON before passing the open SqlConnection to your DbContext constructor. The pattern for BYO-connection in EF core is

SqlConnection con;
public Db(SqlConnection con) 
{
    this.con = con;
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLoggerFactory(loggerFactory)
                  .UseSqlServer(con, o => o.UseRelationalNulls());

    base.OnConfiguring(optionsBuilder);
}
public override void Dispose()
{
    con.Close();
    base.Dispose();
}

or if you're using DI, introduce a IDbConnectionFactory or somesuch and write

public Db(IDbConnectionfactory cf) 
{
    this.con = cf.GetConnection();
}

Passing an open connection to the DbContext constructor will prevent the DbContext from opening and closing the connection for each command. This might slightly increase the size of your connection pool, but it shouldn't be a big deal.