LINQ to SQL doesn't call DELETE

Question

I am deleting a bunch of records from different tables that are linked with foreign keys. When I call 'SubmitChanges' the following error is received:

The DELETE statement conflicted with the REFERENCE constraint FK_PTXProductMap_CustomerProduct". The conflict occurred in database "SOTI", table "dbo.PTXProductMap", column 'InstanceId'. The statement has been terminated.

I looked with profiler what queries are executed when SubmitChanges tries to save changes and DELETE SQL operation is not called for 2 records. I 100% sure that linq2sql-'DELETE' operation is called for them (I put a break point to the line:

IEnumerable<CustomerProduct> products = DbContext.CustomerProducts
    .Where(cp => cp.InstanceId == transition.InstanceId 
            || cp.InstanceId == transition.PreInstanceId);
foreach (CustomerProduct cp in products)
{
    DbContext.CustomerProducts.DeleteOnSubmit(cp);
}

and checked if it was called for required cp object. DELETE is called for 2 another record from the same table... but not for all required

Do you have any ideas why this happened? And how to resolve that?

Any ideas are welcome. Thank you very much!

P.S. I am working with VS2008 SP1, MS SQL 2005 under 64bit Windows 7

P.P.S. I've detected few records in another table that were linked to the deleted scope... including their deletion resolved current error, but it is still unclear why 'DELETE' operation was not generated for ALL records to be deleted.

P.P.P.S. Pretty similar situation for another pair of table: table A contains 1 record, table B - 3 records that refer to the A.1 They are unable to be deleted in one transaction, but if I delete them manually (through the Management Studio) I am able to delete 3 records from B and then record from A... WHY? :(

Answer 1

This article about DeleteOnSubmit contains a note dealing with cascade deletes. We recommend you to delete the child rows first, then there should be no problems with deleting parent entities.