Hangs with LINQ-SQL Server and TransactionScope

Question

I'm encountering a hang when the program tries to access the fruit database. I've already enabled network access MSDTC on both my development computer and the SQL Server server.

Code: (pardon the code coloring...SO's misinterpreting my VB .NET)

Using ts As New TransactionScope
            Dim fruit As New FruitDataContext
            Dim thingies As New ThingiesDataContext
            If (From f In fruit.tblApples Where f.Rotten = "Yes" AndAlso f.batch = 1).Count >= 1 Then
                'Record today's date as the day that the rotten apples were dumped.
            End If

            'Other complicated code that uses ThingiesDataContext and FruitDataContext

            du.SubmitChanges()
            ts.Complete()
End Using

Edit:

I've dug around a bit more and it turns out that the problem lies in the line of LINQ. When I tried to view it with the LINQ to SQL Visualizer, I get the following error:

System.InvalidCastException: Specified cast is not valid.
   at LinqToSqlQueryVisualizer.SqlQueryInfo.deserialize(Stream stream)
   at LinqToSqlQueryVisualizer.Visualizer.Display(IDialogVisualizerService windowService, Stream rawStream)
   at LinqToSqlQueryVisualizer.DialogChooser.Show(IDialogVisualizerService windowService, IVisualizerObjectProvider objectProvider)
   at Microsoft.VisualStudio.DebuggerVisualizers.DebugViewerShim.ManagedShim.DelegatedHost.CreateViewer(IntPtr hwnd, HostServicesHelper hsh, SafeProxyWrapper proxy)

I've also edited the LINQ statement to be closer to my real code.

Final edit: I tried using a normal SqlConnection instead of a "thingies as New ThingiesDataContext" and the problem still occurs.

It appears that TransactionScope cannot handle multiple SQL connections inside the same transaction.

Official Microsoft Note

parallel transactions are not supported by SQL Server.

From MSDN: http://msdn.microsoft.com/en-us/library/bb896149.aspx

Answer 1

This is not an MSDTC issue. If it were, you would get an error saying DTC is not enabled and needs to be. It's also not a deadlock issue, because you would get a specific error about that as well.

If I had to guess, I would say that the 'Other complicated code...' is attempting to perform a database operation and is being blocked by one or the other database context objects.

One way you can determine this is to run SQL Profiler to see what SQL statements are actually being executed on the server, and check for blocks.