Hello all, I’m having some trouble with UnitOfWork and deletes. I have read a few other posts on this and have tried it two different ways and I am still getting errors. Let me explain what I’m doing.

I am inserting rows into (basically) a Join table (the PK is the combination of the two FK’s) with an extra column to indicate the order of the list. When the user rearranges the order (or adds and removes items from the list) I need to update the database. I figured the easiest way to do this is on a save is to just delete any existing rows and re-add whatever is in the list. The list will have less then 10 records. It was either this or check the new list against the db and determine for each record if I needed to 1.) update (the order column) 2) delete the existing db row 3.) add a new row to the db.

The "Delete all – Add all" seamed easier (maybe not as elegant)

So I added a collection to the UnitOfWork for deletion

        
EntityCollection swcpForDelete = DataReductionManager.GetSwcpReductionsForDataRedction(dataReduction.ReducedDataId);

uow.AddCollectionForDelete(swcpForDelete);

Then added items back in. When an item in current list already exists in the db (possibly with a different order value) I always get a SQL exception due to adding a multiple PK, because the collection did not get deleted.

I then noticed a post that recommended I iterate over the collection and add the entities directly to the UnitOfWork. So I changed my code to this...

EntityCollection swcpForDelete = DataReductionManager.GetSwcpReductionsForDataRedction(dataReduction.ReducedDataId);
            foreach (SwcpreducedDataEntity swcpDelete in swcpForDelete)
            {
                uow.AddForDelete(swcpDelte);
            }

I did this and still get the same error.

I have worked around the issue by deleting the collection first and then adding via a transaction, but I wanted to know if I was doing something wrong in my usage of the UnitOfWork class.

Thanks for any insight you can provide.

Chris.

Here is the entire Method

        public static ReducedDataEntity SaveDataReduction(ReducedDataEntity dataReduction, Dictionary<Guid, int> inputs)
        {
            //Create a unit of work that will wrap a transaction for all data updates
            UnitOfWork2 uow = new UnitOfWork2();


            //Get the SwcpreducedDataEntity related to this entity That are currenly in the database
            //& Add them for deletion inside the transaction
            EntityCollection swcpForDelete = DataReductionManager.GetSwcpReductionsForDataRedction(dataReduction.ReducedDataId);
            foreach (SwcpreducedDataEntity swcpDelte in swcpForDelete)
            {
                uow.AddForDelete(swcpDelte);
            }

            //Add the Reduced Data Entity for update in the transaction
            DBAdapter.UpdateStandardDBFields(dataReduction);
            uow.AddForSave(dataReduction, true);

            //Create a new SwcpreducedDataEntity for each entry in the inputs Dictionary
            //add it to be added in the transaction
            SwcpreducedDataEntity newSWCPEntity;
            int inputNumber;
            foreach (Guid key in inputs.Keys)
            {
                inputs.TryGetValue(key, out inputNumber);
                newSWCPEntity = new SwcpreducedDataEntity();
                newSWCPEntity.ReducedDataId = dataReduction.ReducedDataId;
                newSWCPEntity.SwappCommsParameterId = key;
                newSWCPEntity.InputNumber = inputNumber;
                uow.AddForSave(newSWCPEntity);
            }
            try
            {
                using (DataAccessAdapter adapter = DBAdapter.GetNewDataAdapter())
                { 
                    uow.Commit(adapter, true); 
                }
                return dataReduction;
            }
            catch (SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException qryEx)
            {
                System.Diagnostics.Debug.WriteLine(qryEx.QueryExecuted.ToString());
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

You receive your exception when you commit? Can you post the trace for when you execute the commit?

What does this method do DBAdapter.UpdateStandardDBFields(dataReduction);

OK, I will try to provide as much detail as I can..

bclubb wrote:

You receive your exception when you commit? Can you post the trace for when you execute the commit?

What does this method do DBAdapter.UpdateStandardDBFields(dataReduction);

All my tables have Standard Created By , Updated By fields. As I want to take advantange of connection pooling, I use an Applicaton Login to SQL server, so it is not possible to have the database (via a trigger or some other method) capture who updated the data. So before all saves to the database I make a call to this method.

Here is the code for the UpdateStandardDBFields

        public static void UpdateStandardDBFields(EntityBase2 entity)
        { 
            try
            {
                if(entity.IsNew)
                {
                    entity.SetNewFieldValue("CreatedBy",DBAdapter.Identity.NameFirstLast );
                    entity.SetNewFieldValue("UpdatedBy",DBAdapter.Identity.NameFirstLast );
                    entity.SetNewFieldValue("CreatedDate",DateTime.Now );
                    entity.SetNewFieldValue("UpdatedDate",DateTime.Now );
                }
                else
                {
                    if(entity.IsDirty)
                    {
                        entity.SetNewFieldValue("UpdatedBy",DBAdapter.Identity.NameFirstLast);
                        entity.SetNewFieldValue("UpdatedDate",DateTime.Now);
                    }
                }
            }
            catch 
            {
                //if we have an exception that means the current entity does not
                //have the supported properties.  So eat the exception
            }
        }

The Trace code from the UOW.Commit is this.

Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO [CDT_2].[dbo].[SWCPReducedData] ([ReducedData_id], [SWAppCommsParameter_id], [InputNumber]) VALUES (@ReducedDataId, @SwappCommsParameterId, @InputNumber) Parameter: @ReducedDataId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 7e9cbf5a-0c28-4443-bfd4-19c9c4bf520b. Parameter: @SwappCommsParameterId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 964d2e94-e5f8-4f21-aba3-f6d729217415. Parameter: @InputNumber : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll

I am Trapping the exception for debug purposes here is that code

   catch (SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException qryEx)
            {
                System.Diagnostics.Debug.WriteLine(qryEx.StackTrace);
                System.Diagnostics.Debug.WriteLine(qryEx.InnerException.Message);
                System.Diagnostics.Debug.WriteLine(qryEx.InnerException.StackTrace);
                throw;
            } 

The output from 'System.Diagnostics.Debug.WriteLine(qryEx.StackTrace);' is

at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PersistQueue(ArrayList queueToPersist, Boolean insertActions) at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse, Boolean autoCommit) at SD.LLBLGen.Pro.ORMSupportClasses.UnitOfWork2.Commit(IDataAccessAdapter adapterToUse) at CDT.DataManagers.DataReductionManager.SaveDataReduction(ReducedDataEntity dataReduction, Dictionary`2 inputs) in D:\src\ESEA\CDT_V2_Vs2005\DataManagers\DataReductionManager.cs:line 220

The output from "System.Diagnostics.Debug.WriteLine(qryEx.InnerException.Message);" is

Violation of PRIMARY KEY constraint 'SWCPReducedData_PK'. Cannot insert duplicate key in object 'SWCPReducedData'. The statement has been terminated.

The Output from "System.Diagnostics.Debug.WriteLine(qryEx.InnerException.StackTrace);" is

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute()

The code that I got to work is.

            DataAccessAdapter adapter = DBAdapter.GetNewDataAdapter(true);
            try
            {
                adapter.StartTransaction(System.Data.IsolationLevel.ReadCommitted,"DataReductions");
                uowDeletes.Commit(adapter);
                uowUpdates.Commit(adapter);
                adapter.Commit();
                return dataReduction;
            }
            catch (SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException qryEx)
            {
                System.Diagnostics.Debug.WriteLine(qryEx.StackTrace);
                System.Diagnostics.Debug.WriteLine(qryEx.InnerException.Message);
                System.Diagnostics.Debug.WriteLine(qryEx.InnerException.StackTrace);
                throw;
            } 
            finally
            {
                adapter.CloseConnection();
                adapter.Dispose();
            }

You will notice that I am using two unit of work objects to seperate the Deletes and perform them first. Like I said before, I have a work around so this is not a huge pressing issue. I just wanted to know if I was doing something wrong in my usage of the unit of work. This would not be the first time that the problem turned out to be PEBKAC (Problem Exists Between Keyboard And Chair)

Thanks for any insights.

Chris

UnitOfWork objects figure out the order in which actions have to be performed automatically: first Inserts, then Updates and then Deletes.

That's why you get the exception, since the Deletes are kept at last.

In your situation I recommend that you use a Transaction, and you may use UOW inside a Bigger Transaction and Commit the Unit Of Work after you add Entities for deletion, then Commit it again at the end and at last Commit the Bigger Transaction.

Also I don't know if a UOW is a must in your case, but you may have just used a transaction directly.

Walaa wrote:

UnitOfWork objects figure out the order in which actions have to be performed automatically: first Inserts, then Updates and then Deletes.

That's why you get the exception, since the Deletes are kept at last.

In you situation I recommend that you use a Transaction, and you may use UOW inside a Bigger Transaction and Commit the Unit Of Work after you add Entities for deletion, then Commit it again at the end and at last Commit the Bigger Transaction.

Also I don't know if a UOW is a must in your case, but you may have just used a transaction directly.

Walaa,

Thank you for the advice. As I said; I thought the problem might have been me, and it was. If you look at my previous post you will see that I was doing just what you said. I just wanted to confirm that it was my usage and not something else.

Again thanks for a great Product.

Chris