Customizing Save Strategy to avoid Duplicate Key violations

[Jason]

I'm getting a duplicate key constraint violation on a unique index while saving my db context.

Table
  - Id (primary key)
  - Name
  - bool IsActive
  ... (other properites)
  
Constraint is that there can only be a single "Active" record of a given name. I've checked the .rlinq generated from my SqlServer database, and the index is registered as a unique index on Name. The Index in the rlinq file does not list the IsActive column in the mappings, only the Name. In SQL this part of the constraint is accomplished by a Filter where IsActive=true. Not sure if there is a way to carry that over into the DataAccess model.

Here's what i'm trying to do (without breaking it into multiple context changes).
1. Modify existing active record (of Name 'X') to be inactive.
2. Add a new record, also of Name 'X', with IsActive=true
3. SaveChanges on the context.

I can only assume that the new record is getting added before the changes to the old record are saved to the database, resulting in the violation. Is there a way I can handle this without breaking it up into multiple context changes (i.e. modify existing record, SaveChanges, add new record, SaveChanges).

Any help would be appreciated!

[Jan Blessenohl]

Hello Jason,

Telerik Data Access groups the operations by type. First inserts, then updates and third are deletes. This is necessary to have all new data present before we update the foreign key references. Same with deletes, first reset the FKs, then delete the referenced rows.
In some circumstances this order does not work but it is very complicated and time consuming to detect such dependency graphs like yours.
You can always do a FlushChanges() after you have changed the name. We push everything to the server but let the server transaction running. Later (should not be too long because of the server transaction) your insert will succeed. The transactional boundary is still intact.

Regards,
Jan Blessenohl
Telerik