Hi,

i have the following situation. In our datamodel we work with articles, and articles can have a parent-child relation with other articles. We implemented this using a table ARTICLE_RELATION with just 2 foreign keys (AR_ID1 & AR_ID2) to the the ARTICLE table. In LLBLGen i mapped the relations with appropriate names so my ArticleEntity has an ArticleChilds and an ArticleParents collection. Consider the following code:

            UnitOfWork uow = new UnitOfWork();
        
            ArticleEntity a1 = new ArticleEntity(1);
            ArticleEntity a2 = new ArticleEntity(2);
            ArticleRelationEntity r1 = new ArticleRelationEntity();
            a1.ArticleChilds.Add(r1);
            a2.ArticleParents.Add(r1);
            uow.AddForSave(a1,true);
            uow.Commit(new Transaction(IsolationLevel.ReadCommitted,"UOW"),true);
            //At this point we have a record (1,2) in ARTICLE_RELATION
            //re-initialize all unit of work
            uow = new UnitOfWork();
            a1.ArticleChilds.Remove(r1);
            uow.AddForDelete(r1);
            uow.AddForSave(a1,true);
            uow.Commit(new Transaction(IsolationLevel.ReadCommitted,"UOW"),true);
            //At this point we do not have a record (1,2) in ARTICLE_RELATION
            //re-initiialize all unit of work
            uow = new UnitOfWork();
            ArticleRelationEntity r2 = new ArticleRelationEntity();
            a1.ArticleChilds.Add(r2);
            a2.ArticleParents.Add(r2);
            uow.AddForSave(a1,true);
            uow.Commit(new Transaction(IsolationLevel.ReadCommitted,"UOW"),true);
            //At this point we have a record (1,2) in ARTICLE_RELATION

This works completely as expected. But rearranging some code to :

            UnitOfWork uow = new UnitOfWork();
        
            ArticleEntity a1 = new ArticleEntity(1);
            ArticleEntity a2 = new ArticleEntity(2);
            ArticleRelationEntity r1 = new ArticleRelationEntity();
            a1.ArticleChilds.Add(r1);
            a2.ArticleParents.Add(r1);
            uow.AddForSave(a1,true);
            uow.Commit(new Transaction(IsolationLevel.ReadCommitted,"UOW"),true);
            //At this point we have a record (1,2) in ARTICLE_RELATION
            //re-initialize all unit of work
            uow = new UnitOfWork();
            a1.ArticleChilds.Remove(r1);
            uow.AddForDelete(r1);
            ArticleRelationEntity r2 = new ArticleRelationEntity();
            a1.ArticleChilds.Add(r2);
            a2.ArticleParents.Add(r2);
            uow.AddForSave(a1,true);
            uow.Commit(new Transaction(IsolationLevel.ReadCommitted,"UOW"),true);
            //At this point we SHOULD have a record (1,2) in ARTICLE_RELATION but it crashes ...

gives the following error on the last commit :

An unhandled exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in sd.llblgen.pro.ormsupportclasses.net11.dll

Additional information: An exception was caught during the execution of an action query: Violation of PRIMARY KEY constraint 'ARTICLE_RELATION_PK'. Cannot insert duplicate key in object 'ARTICLE_RELATION'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

Why is that ? Shouldn't the order of statements added to the UnitOfWork make sure that the (1,2) record is first deleted, and THEN re-added ?

To put this testcase in the realworld application, we can get this situation when a user removes something from a subcollection (it gets added to the unitofwork for delete), then changes his mind and re-adds it to the subcollection, which will be saved when the main object added to the unitofwork and then commited. This strategy is used throughout our application, and gives no problems, except here, so i guess it has something to do with this "selfreferencing".

Version: I'm still using the 2004.2 version (but with the latest templates & runtimes) but I would rather not upgrade to the 2005 for this project, it's almost finished

UPDATE: i could even simplify the case i described above, the following code also crashed with the PK constraint violation :

            //These first lines tomake sure there is a row (1,2) present in our database in ARTICLE_RELATION
            ArticleRelationEntity r1 = new ArticleRelationEntity(1,2);
            if (r1.IsNew)
                r1.Save();
            
            UnitOfWork uow = new UnitOfWork();
            ArticleRelationEntity r2 = new ArticleRelationEntity(1,2);
            //ASSERT(!r2.IsNew)
            uow.AddForDelete(r2);
            ArticleRelationEntity r3 = new ArticleRelationEntity();
            r3.ArId1= 1;
            r3.ArId2 = 2;
            uow.AddForSave(r3,true);
            uow.Commit(new Transaction(IsolationLevel.ReadCommitted,"UOW"),true);//crashes

Any idea ?

AddForDelete(newEntity) will simply not add the delete command and will also remove the insert command for newEntity, because you want to save it first and after that delete it, and because the entity isn't in the db yet (the entity is new!) removing both commands causes the effect you want to achieve.

The order in which you call commands on the UoW is not important as in: inserts are always first, then updates, then deletes.

The order in which you call commands on the UoW is not important as in: inserts are always first, then updates, then deletes.

ok i understand now. But i do want to achieve that my delete is executed first How do i do that ? Is it possible to start a new transaction somehow, and in that transaction first execute my unitofwork, and then add my main entity's recursive save ?

Somthing like :

Transaction transactionManager = new Transaction(IsolationLevel.ReadCommitted, "Test");
try
{
//currentUnitOfWork contains here all the delete operations the user has ordered through the GUI
m_currentUnitOfWork.Commit(transactionManager ,true);
m_currentUnitOfWork = new UnitOfWork();
m_currentUniOfWork.AddForSave(m_currentEntity,true);
m_currentUnitOfWork.Commit(transactionManager ,true);
transactionManager.Commit();
}
catch
{
transactionManager.Rollback();
}
finally
{
transactionManager.Dispose();
}

would this be correct code ?

Just use 2 UoW's and pass to them the same transaction object . One contains the commands you want to perform first, then in the second you store the commands you want to execute after those. You can use as much UoW's as you want. Just pass hte same transaction object to the UoW's Commit method and specify that autocommit should NOT be performed. (only on the second UoW's)

oh nice, so i could simplify my code to :

try
{
Transaction transactionManager = new Transaction(IsolationLevel.ReadCommitted, "Test");
m_currentUnitOfWork.Commit(transactionManager ,false);//autocommit = FALSE
m_currentUnitOfWork = new UnitOfWork();
m_currentUniOfWork.AddForSave(m_currentEntity,true);
m_currentUnitOfWork.Commit(transactionManager ,true);//autocommit = true
}
catch (Exception ex)
{
//show ex.message
}

and not have to worry about have to call RollBack & Dispose in the catch & finally statements ? That is indeed a very simple and elegant solution to my problem, thanks once again !!

Thats indeed the way it's supposed to work