Version 2.6 Final 26th Aug 2009 Library version: 2.6.9.903 .NET version 3.5 Self Servicing SQL 2005

My problem is that when I try to run the code pasted below I get a timeout error that doesn't happen when I run the code without the Transaction. I have put some tracer codes in there which you can see and it looks like the problem occurs when the second set of 'RangeResponse' rows are deleted, just after the "1" is printed:

The printout is: usersurveys null embed default survey section completed start removing questions 1 2 3 1

I guess it must be because i'm adding another transaction with the same type of entity? Should I rewrite it to try to delete them all in one go rather than a bit at a time?

thanks for any help, Mike

The code is:

      public void DeleteSurvey(int surveyID)
        {
            Transaction transactionManager = new Transaction(IsolationLevel.ReadCommitted, "DeleteSurvey");
            try
            {
                SurveyEntity survey = new SurveyEntity(surveyID);

                UserSurveyCollection userSurveys = new UserSurveyCollection();
                IPredicateExpression where = new PredicateExpression();
                where.Add(UserSurveyFields.UserSurveySurveyId == surveyID);
                transactionManager.Add(userSurveys);
                userSurveys.DeleteMulti(where);
                File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "usersurveys\r\n");

                foreach (EmbedSourceEntity embed in survey.EmbedSource)
                {
                    EmbedSourceEntity tempEmbed = new EmbedSourceEntity(embed.EmbedSourceId);
                    tempEmbed.SetNewFieldValue((int)EmbedSourceFieldIndex.EmbedDefaultSurvey, null);
                    transactionManager.Add(tempEmbed);
                    tempEmbed.Save();
                }
                File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "null embed default survey\r\n");
                //Survey section to use
                //remove only responses to sections that are used once.
                DataTable sectionsToRemove = RetrievalProcedures.DeleteSurveySectionsUsedOnce(surveyID);
                foreach (DataRow sectionToRemove in sectionsToRemove.Rows)
                {
                    int sectionID = (int)sectionToRemove["SurveySectionToUseSurveySectionID"];
                    int surveySectionToUseID = (int)sectionToRemove["SurveySectionToUseID"];

                    //Remove section completed
                    SectionCompletedCollection sectionCompleted = new SectionCompletedCollection();
                    where = new PredicateExpression();
                    where.Add(SectionCompletedFields.SectionCompletedSurveySectionId == sectionID);
                    transactionManager.Add(sectionCompleted);
                    sectionCompleted.DeleteMulti(where);
                    File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "section completed\r\n");

                    SurveySectionEntity section = new SurveySectionEntity(sectionID);
                    if (section.SurveySectionVideoPollId.HasValue)
                    {
                        //remove the video poll

                        //remove raw responses
                        ResponseCollection responses = new ResponseCollection();
                        where = new PredicateExpression();
                        where.Add(ResponseFields.ResponseVideoPollId == section.SurveySectionVideoPollId);
                        transactionManager.Add(responses);
                        responses.DeleteMulti(where);
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "remove responses raw\r\n");

                        //remove responses
                        ResponseRawCollection rawResponses = new ResponseRawCollection();
                        where = new PredicateExpression();
                        where.Add(ResponseRawFields.ResponseRawVideoPollId == section.SurveySectionVideoPollId);
                        transactionManager.Add(rawResponses);
                        rawResponses.DeleteMulti();
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "remove responses\r\n");

                        //remove analysis points
                        AnalysisCollection analyses = section.VideoPoll.Analysis;
                        foreach (AnalysisEntity analysis in analyses)
                        {
                            transactionManager.Add(analysis.AnalysisPoint);
                            analysis.AnalysisPoint.DeleteMulti();
                        }
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "analysis points\r\n");
                        transactionManager.Add(analyses);
                        analyses.DeleteMulti();
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "analysis\r\n");
                        //remove analysis

                        //remove poll
                        section.SetNewFieldValue((int)SurveySectionFieldIndex.SurveySectionVideoPollId, null);
                        transactionManager.Add(section);
                        section.Save();
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "null poll id\r\n");

                        transactionManager.Add(section.VideoPoll);
                        section.VideoPoll.Delete();
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "delete poll\r\n");
                    }
                    else
                    {
                        //remove the questions
                        QuestionCollection questions = section.Question;
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "start removing questions\r\n");
                        foreach (QuestionEntity question in questions)
                        {
                            RangeResponseCollection responses = new RangeResponseCollection();
                            IPredicateExpression responseWhere = new PredicateExpression();
                            responseWhere.Add(RangeResponseFields.RangeResponseQuestionId == question.QuestionId);
                            transactionManager.Add(responses);
                            File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "1\r\n");
                            responses.DeleteMulti(responseWhere);

                            MultipleChoiceCollection mpColl = question.MultipleChoice;
                            foreach (MultipleChoiceEntity mp in mpColl)
                            {
                                MultipleChoiceResponseCollection mpResponse = new MultipleChoiceResponseCollection();
                                IPredicateExpression mpWhere = new PredicateExpression();
                                mpWhere.Add(MultipleChoiceResponseFields.MultipleChoiceResponseMcid == mp.MultipleChoiceId);
                                transactionManager.Add(mpResponse);
                                mpResponse.DeleteMulti(mpWhere);
                            }
                            transactionManager.Add(mpColl);
                            File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "2\r\n");
                            mpColl.DeleteMulti();

                            TextboxCollection textboxes = question.Textbox;
                            foreach (TextboxEntity textbox in textboxes)
                            {
                                TextboxResponseCollection textboxResponse = new TextboxResponseCollection();
                                IPredicateExpression txtWhere = new PredicateExpression();
                                txtWhere.Add(TextboxResponseFields.TextboxResponseTbid == textbox.TextboxId);
                                transactionManager.Add(textboxResponse);
                                textboxResponse.DeleteMulti(txtWhere);

                                CodedCollection codeds = textbox.Coded;
                                foreach (CodedEntity coded in codeds)
                                {
                                    transactionManager.Add(coded.CodedResponse);
                                    coded.CodedResponse.DeleteMulti();
                                }
                                transactionManager.Add(codeds);
                                codeds.DeleteMulti();
                            }
                            transactionManager.Add(textboxes);
                            textboxes.DeleteMulti();
                            File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "3\r\n");
                            MatrixCollection matrixes = question.Matrix;
                            foreach (MatrixEntity matrix in matrixes)
                            {
                                MatrixResponseCollection matrixResponse = new MatrixResponseCollection();
                                IPredicateExpression mxWhere = new PredicateExpression();
                                mxWhere.Add(MatrixResponseFields.MatrixResponseMxid == matrix.MatrixId);
                                transactionManager.Add(matrixResponse);
                                matrixResponse.DeleteMulti(mxWhere);
                            }
                            transactionManager.Add(question.Matrix);
                            question.Matrix.DeleteMulti();

                            transactionManager.Add(question.MatrixSubQuestion);
                            question.MatrixSubQuestion.DeleteMulti();

                        }
                        transactionManager.Add(questions);
                        File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "4\r\n");
                        questions.DeleteMulti();
                    }
                    File.AppendAllText(HttpContext.Current.Server.MapPath("deletedebug.txt"), "5\r\n");
                    SurveySectionToUseEntity sectionEntity = new SurveySectionToUseEntity(surveySectionToUseID);
                    sectionEntity.SetNewFieldValue((int)SurveySectionToUseFieldIndex.SurveySectionToUseSurveySectionId, null);
                    transactionManager.Add(sectionEntity);
                    sectionEntity.Save();

                    transactionManager.Add(section);
                    section.Delete();
                }

                //delete ALL this survey's surveysectiontouse
                transactionManager.Add(survey.SurveySectionToUse);
                survey.SurveySectionToUse.DeleteMulti();

                transactionManager.Add(survey);
                survey.Delete();

                transactionManager.Commit();

            }
            catch (Exception)
            {
                transactionManager.Rollback();
                throw;
            }
            finally
            {
                transactionManager.Dispose();
            }
        }

Stack Trace:

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
   System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
   System.Data.SqlClient.SqlDataReader.get_MetaData() +83
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
   System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
   System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +10
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +86

[ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) +224
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos) +223
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) +420
   Mind.DaoClasses.MultipleChoiceDAO.GetMulti(ITransaction containingTransaction, IEntityCollection collectionToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IEntityFactory entityFactoryToUse, IPredicateExpression filter, IEntity questionInstance, Int32 pageNumber, Int32 pageSize) +95
   Mind.CollectionClasses.MultipleChoiceCollection.GetMultiManyToOne(IEntity questionInstance, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicateExpression filter, Int32 pageNumber, Int32 pageSize) +124
   Mind.CollectionClasses.MultipleChoiceCollection.GetMultiManyToOne(IEntity questionInstance, IPredicateExpression filter) +37
   Mind.EntityClasses.QuestionEntity.GetMultiMultipleChoice(Boolean forceFetch, IEntityFactory entityFactoryToUse, IPredicateExpression filter) +183
   Mind.EntityClasses.QuestionEntity.GetMultiMultipleChoice(Boolean forceFetch) +27
   Mind.EntityClasses.QuestionEntity.get_MultipleChoice() +7
   MindLogic.Survey.DeleteSurvey(Int32 surveyID) +4390
   MindTracker.Admin.Surveys.lnkDelete_Command(Object sender, CommandEventArgs e) +68
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +108
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

That's a lot of code to look at and investigate, maybe you can post much smaller repro code.

Anyway, transaction timeouts are most probably due to database dead locks, so I recommend you use any database monitoring tool to find out what's going wrong. (e.g. SQL Profiler).

Also it seems from your code that you are doing a lot of things and a lot of time consuming actins within a transaction, that's always not recommended.

Try to start the transaction only when you need it, and try to commit it as soon as possible, also try to avoid reads inside a transaction.