"The specified transaction is not associated with the current connection"

[ComptonAlvaro]

I am trying to use the same transaction between many dataContext. I am trying the same code that I am found in the documentation here "The specified transaction is not associated with the current connection"

My code is this:

using (Entities midbContext = new Entities(_optionsDbContext))
{
	using (IDbContextTransaction miTransaccion = await midbContext.Database.BeginTransactionAsync())
	{
		using (Entities midbContext01 = new Entities(_optionsDbContext))
		{
			midbContext01.Database.UseTransaction(miTransaccion.GetDbTransaction());
		}
	}
}

Then problem is in the last line, in the method UseTransaction, it throws an exception that says that "The specified transaction is not associated with the current connection".

I would like to use the same transaction in many dbContext.

Thanks.

[ajcvickers]

@ComptonAlvaro ADO.NET transactions (which you are using here) require that the transaction is associated with a given connection. That is, you can't use the same transaction with different connections. The way to make this work is to manage the connection yourself so it can be used with different contexts. Look at overloads of UseSqlServer (or equivalent for other providers) that take a DbConnection.

[guijealth]

Just a question, because i'm not sure i'm doing that the right way, is it correct to manage sql connections that way?

        private Action<IServiceProvider, DbContextOptionsBuilder> SqlServerConfigurator(IServiceCollection services)
        {
            var connectionString = config.GetConnectionString("SqlServer");
            services.AddScoped(s => new SqlConnection(connectionString));

            return (locator, builder) => {
                var connection = locator.GetRequiredService<SqlConnection>();

                builder.EnableSensitiveDataLogging();

                builder.UseSqlServer(connection, s => s
                       .MigrationsAssembly(typeof(MigrationsDbContext).Assembly.GetName().Name));
            };
        }

[ajcvickers]

@guillerglez88 Possibly, but it's not clear just from that code. Specifically, make sure that the connection is not used by multiple threads concurrently--neither DbContext nor DbConnection are thread safe.

[guijealth]

Thank you for your prompt response. actually it is a concurrent context, it is an asp net core 2 web app, basically i have many db context pointing to same database. I'm trying to manage single db transaction per http request cross dbcontext, so, i'm assuming all DbContext will be registered Scoped (single instance per http request) as well as the SqlConnection, the above code is used that way:

        public void ConfigureServices(IServiceCollection services)
        {
            var configSqlServer = SqlServerConfigurator(services);

            services
                .AddDbContext<MigrationsDbContext>(configSqlServer)
                .AddDbContext<DbContextOne>(configSqlServer)
                .AddDbContext<DbContextTwo>(configSqlServer);
...
        }

In addition to that, i engaged a middleware which begins DbTransaction and share it across dbcontext for each http request.

            var firstDb = dbContexts.First();

            var trn = firstDb.Database.BeginTransaction();
            var dbTrn = trn.GetDbTransaction();

            foreach (var db in dbContexts.Skip(1))
                db.Database.UseTransaction(dbTrn);

[ajcvickers]

@guillerglez88 That sounds like it should be okay. If all of this is happening in a single request, then there should be only one thread executing in that request at any time, which means that even if there are multiple context and a single connection they still won't be used by multiple threads concurrently.

[fabercs]

@guillerglez88 I m not sure your code is working for multiple dbContexts, your configSqlServer delegate is called for each AddDbContext method and inside this delegate you create new SqlConnection for each call. Did you ever test your code for multi different contexts? I am in a similar situation.

[guijealth]

Yes @fabercs, the code is working as expected from the moment i posted the snippets. If you find anything i could be loosing, i would thanks to know.

[amelnikau-boden]

the example is fine, trying to kick it off this on my project.
One ponit is not clear to me: you have 3 dbContexts in the example: MigrationsDbContext, DbContextOne, DbContextTwo; so where did you configured connectionStrings for DbContextOne and ...Two?

[guijealth]

Same connection string for all, i intended to expose only a part of the tables for each db context pointing so the same db.

[amelnikau-boden]

thanks @guillerglez88 for your response. As I investigated there were no any workarounds to tie into one transaction 2 Database changes (with according 2 dbContexts and sqlconnections strings) in EF Core ?

[guijealth]

I'm not able to answer that question, my knowledges about that are very limited. It first should be a capability of the underlaying dbms(sql server, etc), sqlconnection must be able to support it(maybe transaction scopes) as well as EF, specially if you are trying to build linq queries from multiple dbcontexts, if not, maybe you can manage transaction scopes by yourself apart from EF.

[deivydas321]

This is a very good topic as there is no any msdn example how to manage same sql connection with multiple dbcontexts in asp.net core app. Hope the example provided here is a good solution by design.