Version: 2.5.0.0 Final - Adapter

We have a JobEntity with an InvoiceEntity collection

The InvoiceEntity has a Sequence column which has a unique constraint on it

if we have 4 invoices on a job

PK Name Sequence 1 A 1 2 B 2 3 C 3 4 D 4

then reorder to:

PK Name Sequence 1 A 1 2 B 3 3 C 2 4 D 4

we get a constraint violation when the job is saved:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException occurred Message="An exception was caught during the execution of an action query: Violation of UNIQUE KEY constraint 'U_Invoice_Sequence'. Cannot insert duplicate key in object 'dbo.Invoice'.\r\nThe statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="08312007" RuntimeVersion="2.5.0.0" QueryExecuted="\r\n\tQuery: UPDATE [WaterWorksRC1].[dbo].[Invoice] SET [Sequence]=@Sequence WHERE ( ( [WaterWorksRC1].[dbo].[Invoice].[Id] = @Id1) AND ( [WaterWorksRC1].[dbo].[Invoice].[Version] = @Version2))\r\n\tParameter: @Sequence : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.\r\n\tParameter: @Id1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 100.\r\n\tParameter: @Version2 : Binary. Length: 8. Precision: 0. Scale: 0. Direction: Input. Value: binary lob.\r\n" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(List`1 queueToPersist, Boolean insertActions, Int32& totalAmountSaved) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, IPredicateExpression updateRestriction, Boolean recurse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.SaveEntity(IEntity2 entityToSave, Boolean refetchAfterSave, Boolean recurse) at Cwc.WaterWorks.ServiceLayer.WaterWorksServiceLayer.SaveJob(JobEntity job, Boolean refetch, String who) in C:\work\WorkManagement\RC1\WaterWorksServiceLayer\Job.cs:line 596

Do I need to turn-off constraint checking somehow?

Thanks for your help

--Sam

i think the problem is order of execution. the sql would look like this

begin transaction

UPDATE [Invoice] 
SET [Sequence] = 2 
WHERE [Invoice].[Id] = 'B'
 AND [Invoice].[Version] = 1001

UPDATE [Invoice] 
SET [Sequence] = 3
WHERE [Invoice].[Id] = 'C'
 AND [Invoice].[Version] = 1001

commit transaction

the problem is B cannot be updated before C and C cannot be updated before B. Catch 22.

if this table is an m/n relational table you could delete all the job/invoice records and then insert them (instead of update)

As Jason said, I don't think you can do what you are trying to do in raw SQL. Try it in SQL Analyzer. Either you have to drop the contsraint before switching values, and re-load it afterwards. Or at least deete one of them and re-insert it with the new vlaue after updating the other one.

Thanks,

I have solved this by renumbering them all like this....

Before:

PK Name Sequence 1 A 1 2 B 2 3 C 3 4 D 4

Swap B & C and add MAX(Sequence)

PK Name Sequence 1 A 5 2 B 7 3 C 6 4 D 8

not very elegant but it works

--Sam