I developed an application which was doing two LINQ queries in a single
transaction scope and it worked very well in a local area network (LAN)
physically. I am using LINQ to Entities with ADO.NET Entity Framework to access
my database.
The code is shown as follows,
using (var
t = new
TransactionScope(TransactionScopeOption.Required,
new
TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
packets2 = DataContext.RawDataPackets
.Where(p =>
p.ServerTime >= start
&& p.ServerTime < end
&& p.StreamNumber == streamNumberMeeting
&& p.DataSource ==
dataSourceNumber
)
.OrderBy(p => p.ClientTime)
.OrderBy(p => p.ServerTime)
.ToList();
packets.AddRange(packets2);
packets2 =
DataContext.RawDataPackets
.Where(p =>
p.ServerTime >= start
&& p.ServerTime < end
&& p.StreamNumber ==
streamNumberMeeting
&& p.DataSource == dataSourceNumber
)
.OrderBy(p => p.ClientTime)
.OrderBy(p => p.ServerTime)
.ToList();
packets.AddRange(packets2);
t.Complete();
}
Recently I deployed the same application to another computer which only
has access to the database server through a VPN connection and it threw exception after
starting, which said that it cannot connect to the remote server. I was misled
by the message for a while and went to check my DTC (Distributed Transaction
Coordinator) configuaration and all settings looked okay to me.
I rethought around the problem and finally came to a guess that it could
be the DTC which is unable to handle multiple queries in a single transaction
scope on access across physically remote network very well.
Then I modified my code to the following,
using (var
t = new
TransactionScope(TransactionScopeOption.Required,
new
TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
packets2 = DataContext.RawDataPackets
.Where(p =>
p.ServerTime >= start
&& p.ServerTime < end
&& p.StreamNumber ==
streamNumberMeeting
&& p.DataSource == dataSourceNumber
)
.OrderBy(p => p.ClientTime)
.OrderBy(p => p.ServerTime)
.ToList();
t.Complete();
}
packets.AddRange(packets2);
using (var
t = new
TransactionScope(TransactionScopeOption.Required,
new
TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
packets2 = DataContext.RawDataPackets
.Where(p =>
p.ServerTime >= start
&& p.ServerTime < end
&& p.StreamNumber == streamNumberMeeting
&& p.DataSource == dataSourceNumber
)
.OrderBy(p => p.ClientTime)
.OrderBy(p => p.ServerTime)
.ToList();
t.Complete();
}
packets.AddRange(packets2);
Notice I have wrapped those two queries into two separate transaction
scope. Then I tested it. It just worked
!
Subscribe