Object is reinserted into database immediately after delete (DbLinq)

Question

I have a MySql database, whose general structure looks like this:

Manufacturer <== ProbeDefinition <== ImagingSettings
  ElementSettings  ====^ ^==== ProbeInstance

I'm using InnoDB to allow foreign keys, and all foreign keys pointing to a ProbeDefinition have set ON DELETE CASCADE.

The issue I'm having is when I delete a ProbeDefinition in my code, it gets immediately reinserted. The cascading delete happens properly so other tables are cleared, but it seems that the LINQ to SQL may be sending an insert for no reason. Checking the ChangeSet property on the database shows 1 delete, and no inserts.

I'm using the following small bit of code to perform the delete:

database.ProbeDefinition.DeleteOnSubmit(probe);
database.SubmitChanges();

Logs in MySql show the following commands being executed when this is run:

BEGIN
use `wetscoprobes`; DELETE FROM wetscoprobes.probedefinition WHERE ID = 10
use `wetscoprobes`; INSERT INTO wetscoprobes.probedefinition (CenterFrequency, Elements, ID, IsPhased, ManufacturerID, Name, Pitch, Radius, ReverseElements) VALUES (9500000, 128, 10, 1, 6, 'Super Probe 2', 300, 0, 1) 
COMMIT /* xid=2424 */

What could cause this unnecessary INSERT? Note that deleting a Manufacturer in the exact same way deletes correctly, with the following log:

BEGIN 
use `wetscoprobes`; DELETE FROM wetscoprobes.manufacturer WHERE ID = 9 
COMMIT /* xid=2668 */ 

Edit: Upon further testing, it seems that this only happens after I've populated a ListBox with a list of ProbeDefinitions.

I tried running the above delete code before and after the following snippet had run:

var manufacturer = (Manufacturer)cbxManufacturer.SelectedItem;
var probes = manufacturer.ProbeDefinition;

foreach (var probe in probes)
{
    cbxProbeModel.Items.Add(probe);
}

The object gets deleted properly before said code has run, but anytime after this point, it performs an insert after the delete. Does it not like the fact that the object is referenced somewhere?

Here's the code I'm running to test deleting a definition from the intermediate window:

database.ProbeDefinition.DeleteOnSubmit(database.ProbeDefinition.Last())
database.SubmitChanges()

Answer 1

Turns out there are issues when there are multiple references to your object. Stepping through the DbLinq source, I learned that after a DELETE is completed, it steps through all other "watched" objects, looking for references.

In this case, I have multiple references through the table database.ProbeDefinition as well as through the manufacturer reference, manufacturer.ProbeDefinition. This isn't an issue till I have accessed objects through both methods. Using Remove can delete the reference from manufacturer, using DeleteOnSubmit will delete the the object from the table. If I do one or the other, the other reference still exists, and thus the object is marked to be reinserted. I'm not sure if this is a bug in DbLinq that it doesn't delete other references, or expected behavior.

Either way, in my case, the solution is to either access the table using only a single method, and delete using that method, or to delete using both methods. For the sake of getting it working, I used the second method:

// Delete probe
this.manufacturer.ProbeDefinition.Remove(probe);
database.ProbeDefinition.DeleteOnSubmit(probe);
database.SubmitChanges();

EDIT: Upon further work on the project and similar issues, I have found the true underlying issue of my implementation. I have a long-lived DataContext, and with how caching works (to make SubmitChanges work), you can't do this. The real solution is to have a short-lived DataContext, and reconnect to the database in each method.