This is a tough question to word...so bear with me.
I have two tables in my database, [Item] and [AssignedProperty]. [AssignedProperty] is the child in a parent-child relationship with [Item]. So it basically just has an ItemID field and a foreign key relationship utilizing it. [AssignedProperty] also has an identical relationship with another table named [Property], and its own primary key, AssignedPropertyID. This creates a nice many-to-many relationship. I have a constraint however, that dictates that one [AssignedProperty] cannot have duplicate occurrences of ItemID and PropertyID in the same record. Therefore an [Item] can only have one of each [Property].
This works nicely, but with LINQ-To-SQL, theres an issue in what I think is the order of execution that conflicts with this constraint.
To produce this error, I load up an [Item] in my application using LINQ-To-SQL. I then select an [AssignedProperty] object, from the item's [AssignedProperties] collection and delete it. I then create a new [AssignedProperty] object with the exact same ItemID and PropertyID as the one I just deleted, and add it back to the collection. When I call [SubmitChanges] on the DataContext, it will flag me on the constraint. If the old [AssignedProperty] record was deleted first, then there is no reason why the new one should produce that error. It looks like it's attempting to insert the new [AssignedProperty] before deleting the old one.
How should I fix this, and in the database or my app? TIA