Avoid DTC call in Entity Framework 4 on SQL Server 2005

Ok really went down a rabbit hole today but I managed to crawl back out.  I went to create a database connection test. It consisted of first creating a transaction and then select a count of the number of records that match the record I was about to add, add the record, then count again to make sure the count is now different from the first count.

This triggered a distributed transaction coordinator(DTC) error because I didn't have distributed transactions turned on but I didn't want it to start a distributed transaction as they add processing time and are not needed in this scenario.  So after much research I've discovered this apparently wouldn't have occurred if I was using SQL Server 2008 or greater but I of course was connecting to 2005.

In Entity Framework 6(EF6) it looks like they are finally giving us the option to tell the Entity Framework to use an existing transaction and not force a distributed one. Which will be nice when EF6 is released and I upgrade to it.

Until then the way to get Entity Framework 4 to use a single connection and single transaction and not trigger a DTC call is to open a connection manually and pass it into the context when you create it and tell it to not own the connection. Here is an example code:

        Using connection As New EntityConnection("name=MyEntities") 
            connection.Open() 
            Using context As New MyEntities(connection, False)
                Using transaction As New TransactionScope
                    Dim repository As MyRepository = New MyRepository(context)
                    Dim target As MyServices = New MyServices(repository, New InfrastructureFactory())
                    Dim record As MyFileRecord = New MyFileRecord()

                    Dim beforeRecordCount As Integer = GetRecord1Count(context)

                    record.CNTL_ID = Record1_CNTL_ID 
                    record.FAST_PATH = Record1_FAST_PATH 
                    record.CUST_NUM = Record1_CUST_NUM 
                    record.AGT_ID = Record1_AGT_ID 
                    record.AGT_ID2 = Record1_AGT_ID2 
                    record.CVG_TYPE = Record1_CVG_TYPE 

                    target.CreatePolicyInfoRecord(record) 

                    Dim afterRecordCount As Integer = GetRecord1Count(context)

                    Assert.AreNotEqual(beforeRecordCount, afterRecordCount) 
                End Using 
            End Using 
            connection.Close() 
        End Using