Transaction exception. Delete and insert in one transaction.

[Tsybulnikov]

Hi, I use Telerik Data Access Q2 2014 SP1 with FireBird Provider for dotNET.
I need to do next: delete rows from table and insert agains in one transaction.  ID - it's primary key without auto generate trigger/generator.

1) If I use SaveChanges() to control transaction throw "InvalidOperationException: 130 instances faild to persist":

             var newPartners = webService.Partners.ToList().Select(p => (PARTNER)p);
           AbonDataEntitiesModel dataContext = new AbonDataEntitiesModel(_connectionString);
           dataContext.Delete(dataContext.PARTNERs.ToList());
           dataContext.Add(newPartners );
           dataContext.SaveChanges();

2) If I use TransactionScope throw "TransactionAbortedException: violeation of PRIMARY KEY constraint".

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required,
    new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted }))
{
    offlineDataManager.DeletePartners();
    var newPartners = webService.Partners.ToList().Select(p => (PARTNER)p);
    offlineDataManager.AddPartners(newPartners);
    ts.Complete();
}

How can I delete and insert rows in one transaction?

[Ralph Waldenmaier]

Hello Tsybulnikov,
In general using the first approach should work. Though, I have some questions:

- Are the objects returned by your webservice of type PersistenceCapable and are know by your specified Domain Model?
- What exactly is the exception message you get?
- Do you have primary key clashes? By this I mean are you deleting for instance the Partner with the Id 1 and later Add a newPartner with the same Id? This would be a clash in the definition and will not work.

I am looking forward to hearing from you soon and getting this solved with you.

Regards,
Ralph Waldenmaier
Telerik

 

 

[Tsybulnikov]

1) Objects returned by server are converted into data model objects (PARTNER). And then marked to delete.
2) Full exception message:

Telerik.OpenAccess.Exceptions.InvalidOperationException: 130 instances failed to persist   at Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e)   at OpenAccessRuntime.ExceptionWrapper.Throw()   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.handleException(Exception x, Boolean needsRollback)   at OpenAccessRuntime.DataObjects.OpenAccessPersistenceManagerImp.makePersistentAll(ICollection pcs)   at OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.makePersistentAll(ICollection collection)   at OpenAccessRuntime.DataObjects.PMProxy.makePersistentAll(IEnumerator enr)   at OpenAccessRuntime.ObjectScope.Add(Object pc)   at Telerik.OpenAccess.OpenAccessContextBase.Add(Object entity)   at Telerik.OpenAccess.OpenAccessContextBase.Add(IEnumerable entities)   at PPSAddInMain.Models.OfflineDataManager.RefreshPartners(IEnumerable`1 partners) in d:\Projects\EasPlugin\PPSAddInMain\Models\OfflineDataManager.cs:line 203   at PPSAddInMain.ViewModels.PartnersListVM.UpdatePartners() in d:\Projects\EasPlugin\PPSAddInMain\ViewModels\PartnersVM.cs:line 246

3) Yes. I have primary clashes, but how can I do my script in one transactions: a) delete all PARTNERs b) Insert new PARTNERs with same ID (primary key)? This is the main question. Using Entity Framework I can do it with TransactionScope  like in the second approach.

[Ralph Waldenmaier]

Hello Tsybulnikov,
Thank you for providing more details here.
The problem is because you are deleting and adding objects that have the same primary key specified. This is not supported by Telerik Data Access.

Though, there is a way to deal with such situations. To achieve this, you need to:

1. Obtain all objects that should be deleted and added that belong together( via the primary key)
2. apply the values of the 'new' objects to the obtained 'old' objects
3. remove those objects from your delete and new objects lists
4. delete the objects that are not affected by the new ones
5. add the new objects to the context as well

By following this approach, you are basically merging the values from the new objects to the old objects without the need to delete and recreate them. An update will be triggered for those objects in the database.

I hope this information is helpful for you.
Do come back in case you need further assistance.

Regards,
Ralph Waldenmaier
Telerik

 

 

[Tsybulnikov]

Hello Ralph Waldenmaier,

Thank you. I understand your  workaround approach. It's just updating method.


But answer to my second question. Why I can't use TransactionScope - second approach in first post?

[Ralph Waldenmaier]

Hello Tsybulnikov,
Yes you can also use the transaction scope approach, but I have to add, that this is the 'old' way to work with Telerik Data Access. It is replaces with the context approach as mentioned before.

Please see this link for details of how to work with the TransactionScope. In order to the an IObjectScope from a context, you have to create a partial class for your context and expose the IObjectScope that you can obtain from the OpenAccessContextBase.

Hope this helps.


Regards,
Ralph Waldenmaier
Telerik