Efcore.pg: TransactionScope Support

[thedumbtechguy]

So my foray into finding the answer for this has been difficult and fraught with many conflicting answers.

Will npgsql support transaction scopes in net core 2.0?
I know it's possible DTC won't be supported, but at least, will we get support for LTM?
I can write my code to not use multiple connections so if this is possible, it'll be really great.

NB: I don't even know the full extent for the support for TransactionScope in net core 2.0 so pardon me.

[roji]

@thedumbtechguy I think part of the confusion is about .NET Core/.NET Standard 2.0 and EF Core 2.0.

The current version of Npgsql already targets .NET Standard 2.0, so TransactionScope is fully supported there. As you said there's no support for distributed transactions with the DTC (because it's not cross-platform), but you can definitely use TransactionScope for ambient transactions etc.

However, at the moment EF Core itself doesn't support TransactionScope as far as I can tell - the issue tracking this is https://github.com/aspnet/EntityFramework/issues/5595. You can ping the team on that issue to see what they're planning. IMHO it makes sense for EF Core 2.0 to add support since it will be released at the same time as .NET Standard 2.0, but it may be too late already.

Hope that clears up your question, am going to close this as there's nothing to do on my side, but feel free to continue asking questions etc.

[thedumbtechguy]

I am not using EF. I am using Dapper.
From your answer, I guess this means ensuring my transactions don't get promoted to distributed will work but I am confused about the reference to EF.
Do I need to use EF to take advantage of TransactionScope?

Also is there some warning mechanism in the event I accidentally promote my transactions like EF does when Enlisted scopes are used?

[roji]

Not at all, I only mentioned EF because you opened this issue on the Npgsql EF Core repo.

If you're using Dapper you should be fine. As long as you're not trying to involve more than one database everything should work.

[thedumbtechguy]

Awesome! Thank you very much.

[pkarthik510]

FYI new visitors, for TransactionScope to be working as expected make sure that "Enlist" key is set to true in PostgreSQL connection string (Enlist=True).

[PaulDMendoza]

For future reference, this is how you can use TransactionScope and EntityFramework together and Dapper.

This is with Npgsql.EntityFrameworkCore.Postgresql 2.1.2 and Npgsql 4.0.3

I was actually able to get TransactionScope to work without having to use Enlist.

using (TransactionScope tx = new TransactionScope())
            {
                using (var cn = new NpgsqlConnection(connStr))
                {
                    cn.Open();

                    var userCount = cn.ExecuteScalar<int>("select count(0) from user");

                    var context = Context.CreateFromConnection(cn);

                    context.users.Add(new user { id = Guid.NewGuid(), name = "Test name " + DateTimeOffset.UtcNow.ToString() });
                    context.SaveChanges();


                    cn.Execute("insert into \"user\" (id, name) VALUES (:id, :name);", new { id = Guid.NewGuid(), name = "Second contacts " + DateTimeOffset.UtcNow });

                    var users = context.users.ToList();
                    Console.WriteLine(users.Count);
                }

                tx.Complete();
            }

            using (var cn = new NpgsqlConnection(connStr))
            {
                cn.Open();
                var context = Context.CreateFromConnection(cn);
                Console.WriteLine(context.users.Count());
            }

This is my DBContext and model. Notice I have this special method to use an existing connection.

public class Context : DbContext
    {
        public Context(DbContextOptions<Context> options) : base(options)
        {

        }

        public static Context CreateFromConnection(IDbConnection teamconnection)
        {
            var optionsBuilder = new Microsoft.EntityFrameworkCore.DbContextOptionsBuilder<Context>();

            optionsBuilder.UseNpgsql((DbConnection)teamconnection);

            var _teamdbcontext = new Context(optionsBuilder.Options);
            return _teamdbcontext;
        }

        public DbSet<user> users { get; set; }



        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            var e = modelBuilder.Entity<user>();
            e.ToTable("user");
            e.HasKey(k => new { userid = k.id });
        }

    }

    public class user
    {
        public Guid id { get; set; }
        public string name { get; set; }
    }