Two DbContext's on same server throws: This platform does not support distributed transactions

Question

I am not able to figure out why TransactionScope is starting distributed transaction (which is not configured at the SQL Server). I would like to use local transaction instead, which can be used when two databases are located in the same SQL Server instance. What is wrong with my code, how can I fix it? Can I force Transaction Scope to try local transaction first?

Databases

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=DESKTOP;Initial Catalog=test;Integrated Security=True",
    "Test2Connection": "Data Source=DESKTOP;Initial Catalog=test2;Integrated Security=True"
  }
}

startup.cs registering TestContext and Test2Context

services.AddDbContext<TestContext>(options =>
 options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

services.AddDbContext<Test2Context>(options =>
 options.UseSqlServer(Configuration.GetConnectionString("Test2Connection")));

services.AddTransient<ICustomerRepository, CustomerRepository>();
services.AddTransient<IMaterialRepository, MaterialRepository>();

// This service inject TestContext and Test2Context
services.AddTransient<ICustomerService, CustomerService>();

services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

CustomerRepository using TestContext

public class CustomerRepository : ICustomerRepository
    {
        private readonly TestContext _context;
        public CustomerRepository(TestContext context)
        {
            _context = context;
        }
        public Customer Retrieve(int id)
        {
            return _context.Customers.Where(x => x.Id == id).FirstOrDefault();
        }
    }

MaterialRepository using Test2Context

public class MaterialRepository : IMaterialRepository
    {
        private readonly Test2Context _context;
        public MaterialRepository(Test2Context context)
        {
            _context = context;
        }
        public Material Retrieve(int id)
        {
            return _context.Materials.Where(x => x.Id == id).FirstOrDefault();
        }
    }

CustomerService

public class CustomerService : ICustomerService
    {
        private readonly ICustomerRepository _customerRepository;
        private readonly IMaterialRepository _materialRepository;
        public CustomerService(
            ICustomerRepository customerRepository, 
            IMaterialRepository materialRepository)
        {
            _customerRepository = customerRepository;
            _materialRepository = materialRepository;
        }
        public void DoSomething()
        {
            using (var transaction = new TransactionScope(TransactionScopeOption.Required
               //,new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }
                ))
            {
                var customer = _customerRepository.Retrieve(1);
                var material = _materialRepository.Retrieve(1); // The exception is thrown here !
                // _customerRepository.Save(customer);
                transaction.Complete();
            }
        }
    }

reading from second context throw's This platform does not support distributed transactions exception.

The distributed transaction is also firing, when using the same connection string for two database contexts

startup.cs

services.AddDbContext<TestContext>(options =>
                 options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

services.AddDbContext<TestReadOnlyContext>(options =>  
                options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

CustomerReadOnlyRepository

public class CustomerReadOnlyRepository : ICustomerReadOnlyRepository
    {
        private readonly TestReadOnlyContext _context;
        public CustomerReadOnlyRepository(TestReadOnlyContext context)
        {
            _context = context;
        }
        public Customer Retrieve(int customerId)
        {
            Customer customer = _context.Customers.Where(x => x.Id == customerId).Include("Offices").FirstOrDefault();

            return customer;
        }
    }

CustomerService

var customer = _customerRepository.Retrieve(1);
var customerReadOnly = _customerReadOnlyRepository.Retrieve(1); // Throw's the same error.

Answer 1

why TransactionScope is starting distributed transaction?

Because you have two different SQL Server Sessions. The client has no way to coordinate transactions on separate sessions without promoting the transaction to a distributed transaction.

Can I force Transaction Scope to try local transaction first?

If you use a single Sql Server session for both DbContext instances, then it won't need to promote to a distributed transaction.

You should be able to simply use identical query strings for both DbContexts, and SqlClient will automagically cache and reuse a single connection for both. When a SqlConnection enlisted in a Transaction is Close() or Disposed() it's actually set aside pending the outcome of the transaction. Any subsequent attempt to open a new SqlConnection using the same connection string will return this same connection. A DbContext will, by default, Open and Close the SqlConnection for each operation, so it should benefit from this behavior.

If the same connection string doesn't work, you might have to open the SqlConnection and use it to construct both DbContext instances.

But wait, the tables are in different databases. Yep, and if there's a good reason for that you can leave them there. You need to do some work to enable a single SqlConnection to access the objects in both databases. The best way to do this is to CREATE SYNONYMs so your application can connect to a single database and access the remote objects with local names. This also enables you to have multiple instances of your application on a single instance (handy for dev/test).