Visual Studio LightSwitch can't update Transaction error

[rasheed]

Hi,

I'm trying to update an entity with VisualStudio LightSwitch and I receive this error :

An error occurred while starting a transaction on the provider connection. See the inner exception for details.
Inner exception message:
Local transaction can not be started while in a distributed transaction.

I've already search forums, google and can't find any solution. I thing it's a dotConnect issue.

I have dotConnect for Oracle 6.10 Professional.

Thank you for support!

[rasheed]

I found a workaround, but I don't know if it's the best solution.. What do you think?

Code: Select all

    public partial class FooService
    {
        partial void SaveChanges_Executed()
        {
            tx.Complete();
            tx.Dispose();
        }

        private TransactionScope tx;

        partial void SaveChanges_Executing()
        {
            tx = new TransactionScope(
               TransactionScopeOption.Required,
               new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted });
        }
    }

So I don't know how LightSwitch starts the trasaction save pipeline (original article).

[Edit]We have added tx.Dispose() in the body of the SaveChanges_Executed method because data is sent to the server on transaction's dispose.[/Edit]

[rasheed]

Just a precision, this is the inner exception that I receive without the workaround :

Code: Select all

Message : An error occurred while starting a transaction on the provider connection. See the inner exception for details.
Source : System.Data.Entity
StackTrace :    at System.Data.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.EntityClient.EntityConnection.BeginTransaction()
   at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DataProvider.LinqToEntitiesDataProvider`1.SubmitCore(IEnumerable`1 changes)
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DataProvider.DataProvider.Submit(IEnumerable`1 changes)
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceImplementation`1.PerformPersistCore(IEnumerable`1 eventsChangeSetItems)
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceImplementation`1.c__DisplayClass49.b__48()
   at Microsoft.LightSwitch.Threading.DualDispatcherObject.Mutate(IDispatcher logicDispatcher, MutatorHost host, Action mutator)
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceImplementation`1.PerformPersist(IEnumerable`1 eventsChangeSetItems)
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceImplementation`1.Microsoft.LightSwitch.ServerGenerated.Implementation.IServerDataServiceImplementationCore.Submit()
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DomainService`1.InvokeSubmit()
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DomainService`1.b__17()
   at Microsoft.LightSwitch.ServerGenerated.Implementation.DataServiceImplementation`1.Microsoft.LightSwitch.ServerGenerated.Implementation.IServerDataServiceImplementationCore.InvokeOperation[T](String operationName, Object[] args, Boolean invokedFromClient, Func`1 invokeOperation, Action catchCallback)

and the inner inner exception

Code: Select all

Message: Local transaction can not be started while in a distributed transaction.
Source : Devart.Data.Oracle
StackTrace :    at Devart.Data.Oracle.OracleTransaction..ctor(OracleConnection A_0, IsolationLevel A_1)
   at Devart.Data.Oracle.OracleConnection.BeginTransaction(IsolationLevel il)
   at Devart.Data.Oracle.OracleConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
   at System.Data.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)

[AndreyR]

Thank you for the report. We are investigating the issue.
I will let you know about the results of our investigation.

[pbain]

rasheed,
Are you still using this work-around? Where do you insert this work-around code?

Thanks!

[lsoljf]

pbain

I used rasheed solution. What I did was (not sure this is the best, but worked):

Choosed the "File View", instead of "Logical View" in top of Solution Explorer.

Selected the Server project.
Show All files can be smart to select, because you then can find the DataService.cs file in the GeneratedArtifacts folder, and find out the name you shold use when adding the new partial class.
Added a new Class file in the GeneratedArtifacts folder. In that file I pase the code.
I also added a reference to System.Transactions, and added using System.Transactions; in the new class.

I got one problem, when I used the code, and that was that I could not save it twice. My solution to this was to add tx.Dispose(); after tx.Complete();

My Code is now:
public partial class [name of the class you want to override methodes, found in DataService class]
{
partial void SaveChanges_Executed()
{
tx.Complete();
tx.Dispose();
}

private TransactionScope tx;

partial void SaveChanges_Executing()
{
tx = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted });
}
}

Hope this will help you to.
God Luck!

[Shalex]

SQL Server, unlike Oracle and some other servers, allows to use local transactions within a global transaction. And our
provider generates the exception as Oracle prohibits this scenario.

There is the same situation with LightSwitch: there is an attempt to start a local transaction within the global one. Be aware
that System.Data.Entity.dll from EF June 2011 CTP (unlike .NET4.0) includes the fix for this problem: no attempt to
use a local transaction inside the global one.

So, we consider this behaviour as designed. A workaround is mentioned in Rasheed's second post in the current
thread. Most likely the fix (corrected System.Data.Entity.dll) will be included into .NET 4.5. As a result, no workaround
will be necessary.

If EF June 2011 CTP is installed on your workstation, no workaround is needed as well. But in this case there is another
problem: EF June 2011 CTP is not compatible with the design time of LightSwitch Wizard when adding new entities basing
on the tables from database although run-time works (is compatible). Here is a workaround for generating a model when
using EF June 2011 CTP with LightSwitch:
- navigate to GAC .NET 4 (\Windows\Microsoft.NET\assembly\GAC_MSIL\)
- move the "policy.4.0.system.data.entity" folder to some temp folder (to avoid redirect System.Data.Entity.dll v 4.0 ->
System.Data.Entity.dll v 4.2).
=> LightSwitch Wizard works successfully.
Then move the "policy.4.0.system.data.entity" folder back to GAC .NET 4 to get a workable run-time.

[G_man]

I was having the same error, but the issue was not with lightswitch nor the db connection I created. I spent almost 2days trying to figure it out, checking forums and trying out the suggestions. It was actually a problem within the code of the procedure I was accessing via the lightswitch interface.
If you're accessing some custom server code on your database, debug the code to make sure it's not causing the error. a possible indication that it maybe a code problem within the database, is if the interface'times out' or throws the same 'failure to enlist transaction' error and the debugger indicates an error at the 'con.executeNonQuery()' method.