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:
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
Comments
Post a Comment