Hi,

i have the following problem ..in this long-running project (started with LLBLgen 1.2004, along the way upgraded to 1.2005) I have somewhere a form with pricing administration where a user can delete a price entity from the database. Since the priceID PK is a FK in the "PRICE_LOCALE" table, i implemented it as follows :

    m_currentUnitOfWork.AddCollectionForDelete(m_currentPrice.PriceLocale);
    m_currentUnitOfWork.AddForDelete(m_currentPrice);

The later on when the user closes the form by clicking "OK", the unit of work gets commited, if he click "cancel" the unit of work is discarded. This worked very well until now ... There is a new table added (RECEPTION_STATUS) which also uses the FK priceID, so when deleting a price entity, records in this table also have to be deleted. No problem i thought, and added the code :

    m_currentUnitOfWork.AddCollectionForDelete(m_currentPrice.ReceptionStatus);
    m_currentUnitOfWork.AddCollectionForDelete(m_currentPrice.PriceLocale);
    m_currentUnitOfWork.AddForDelete(m_currentPrice);

But now when the unit of work get commited, the following exception is thrown:

An exception was caught during the execution of an action query: DELETE statement conflicted with COLUMN REFERENCE constraint 'PRICE_RECEPTION_STATUS_FK1'. The conflict occurred in database 'AGRO', table 'RECEPTION_STATUS', column 'PRICE_ID'.
The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause

Further inspection of what is going on led me to commenting out the lines as such :

    int i = m_currentPrice.ReceptionStatus.Count;
    m_currentUnitOfWork.AddCollectionForDelete(m_currentPrice.ReceptionStatus);
    //m_currentUnitOfWork.AddCollectionForDelete(m_currentPrice.PriceLocale);
    //m_currentUnitOfWork.AddForDelete(m_currentPrice);

so only records in the ReceptionStatus table should be deleted, but guess what, not a single record is deleted. And m_currentUnitOfWork.Commit does not throw an exception or anything. By adding the int i = m_currentPrice.ReceptionStatus.Count i could see that the collection is definately not empty. Any explanation for this ? One difference i notice between the PRICE_LOCALE and RECEPTION_STATUS table is that in RECEPTION_STATUS the FK PRICE_ID is having "allow nulls" and in PRICE_LOCALE it is not ...but that shouldn't make a difference ?

Update: i also tried the following code:

    int i = m_currentPrice.ReceptionStatus.Count;
    //m_currentUnitOfWork.AddCollectionForDelete(m_currentPrice.ReceptionStatus);
    foreach (ReceptionStatusEntity rs in m_currentPrice.ReceptionStatus)
        m_currentUnitOfWork.AddForDelete(rs);
    //m_currentUnitOfWork.AddCollectionForDelete(m_currentPrice.PriceLocale);
    //m_currentUnitOfWork.AddForDelete(m_currentPrice);

but not a single record gets deleted from the database ... (although I can step in the for-loop and see the receptionstatus entities getting added to the uniofwork)

Using : LLBLGen 1.2005 - SelfServicing templates for SQLserver / VS.NET 2003 VS.NET 2003 .NET 1.1 SQLServer 2000

Could you please show us the SQL statements generated by the code to delete the ReceptionStatus Collection?

Also did you try to delete the collection without using the UnitOfWork?

As a side note: I always go in favour of the database implemented Cascade Delete in such situations

hm ..another "small" detail i overlooked ..the RECEPTION_STATUS table does not have a primary key (on purpose)

oops

please check the following thread: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3868

Ok , i checked the cascade delete on the relationship between PRICE & RECEPTION_STATUS and now it works fine.

But on a sidenote, shouldn't the unit of work have been able to handle the delete ? As in the thread you pointed me to, Otis say "insert" and "update" won't work without a PK (which is no problem). Th situation is like this, the RECEPTION_STATUS table is daily regenerated completely using batchjobs. So the generated datalayer will never directly insert or update records, only use them for reporting purposes. But i'm suprised the delete didn't work, because if I "quickwatch" my m_currentUnitOfWork right before Commit() it gives a positive number of "ObjectsToBeDeleted" and then doesn't delete any and also doens't give an error. Oh well, life is full of surprises Thnx for the replies !

Ok the UnitOfWOrk might hold object for deletion. But how will you delete those objects if you don't have an index (PK) in order not to delete all the records in the database.

I mean how would you write the SQL statement to be excuted?!!

An alternative to cascading delete might be to use a UOW AddDeleteEntitiesDirectlyCall which would allow you to specify your own filter bucket instead of depending on a PK.