Hello,

We encounter a strange LLBLGen error while using a standard query, the following are the full details, Your quick response will be highly appreciated !

Important issues :

1. While running the query directly at our DB, it's running without any problems, and it's running extremely fast.

2. We saw that when the query return around 20 record, everything works perfectly even with LLBLGen, But more than 30 record, LLBLGen code throws an exception, even though running the query directly at the DB, it's very fast.

3. We already had similar problem, that was solved by your developers, because it was LLBLGen bug,

   Reference to the previous case : http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14074&HighLight=1

Our code

        VisitEntity visit = new VisitEntity(visitId);
        PrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.VisitEntity);
        IPrefetchPathElement2 elementQmVisit = VisitEntity.PrefetchPathPatientQmVisitCollection;
        elementQmVisit.Filter = new PredicateExpression((PatientQmVisitFields.QmClinicalStatusId == statusToGet));
        IPrefetchPathElement2 elementDiag = elementQmVisit.SubPath.Add(PatientQmVisitEntity.PrefetchPathPatientQmVisitDiagnosisCollection);
        IPrefetchPathElement2 elementQM = elementQmVisit.SubPath.Add(PatientQmVisitEntity.PrefetchPathQualityMeasureByQualityMeasureId);

        IPrefetchPathElement2 elementPatient = VisitEntity.PrefetchPathPatientByPatientId;
        elementPatient.SubPath.Add(PatientEntity.PrefetchPathPracticePatientCollection);
        IPrefetchPathElement2 elementPhysician = VisitEntity.PrefetchPathUserByUserId;
        elementPhysician.SubPath.Add(UserEntity.PrefetchPathPracticeUserCollection);
        IPrefetchPathElement2 elementItems = elementQmVisit.SubPath.Add(PatientQmVisitEntity.PrefetchPathPatientQmVisitItemCollection);

        elementItems.SubPath.Add(PatientQmVisitItemEntity.PrefetchPathPatientQmVisitItemCodeCollection);


        //add the element that was created
        prefetch.Add(elementQmVisit);
        prefetch.Add(elementPhysician);
        prefetch.Add(elementPatient);

Exception Details

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled by user code Message="An exception was caught during the execution of a retrieval query: ORA-01013: user requested cancel of current operation. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception." Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20" RuntimeBuild="01162009" RuntimeVersion="2.6.0.0" QueryExecuted="\r\n\tQuery: SELECT \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"VISIT_ID\" AS \"VisitId\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"QUALITY_MEASURE_ID\" AS \"QualityMeasureId\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"ITEM_ID\" AS \"ItemId\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"ITEM_CODE_ID\" AS \"ItemCodeId\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"CODE\" AS \"Code\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"CODE_SYSTEM_ID\" AS \"CodeSystemId\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"EXCLUSION_MODIFIER_ID\" AS \"ExclusionModifierId\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"CREATE_DATE\" AS \"CreateDate\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"CREATE_BY\" AS \"CreateBy\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"UPDATE_DATE\" AS \"UpdateDate\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"UPDATE_BY\" AS \"UpdateBy\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"TRANSACTION_ID\" AS \"TransactionId\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"CODE_DESCRIPTION\" AS \"CodeDescription\", \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"PRACTICE_ID\" AS \"PracticeId\" FROM \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\" WHERE ( \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"VISIT_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"VISIT_ID\" AS \"VisitId\" FROM \"PMDS\".\"PATIENT_QM_VISIT_ITEM\" WHERE ( ( \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"VISIT_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" AS \"VisitId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId1)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId2))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"QUALITY_MEASURE_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"QUALITY_MEASURE_ID\" AS \"QualityMeasureId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId3)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId4))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"QM_REPORTING_METHOD_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_REPORTING_METHOD_ID\" AS \"QmReportingMethodId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId5)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId6)))))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"QUALITY_MEASURE_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"QUALITY_MEASURE_ID\" AS \"QualityMeasureId\" FROM \"PMDS\".\"PATIENT_QM_VISIT_ITEM\" WHERE ( ( \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"VISIT_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" AS \"VisitId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId7)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId8))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"QUALITY_MEASURE_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"QUALITY_MEASURE_ID\" AS \"QualityMeasureId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId9)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId10))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"QM_REPORTING_METHOD_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_REPORTING_METHOD_ID\" AS \"QmReportingMethodId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId11)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId12)))))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM_CODE\".\"ITEM_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"ITEM_ID\" AS \"ItemId\" FROM \"PMDS\".\"PATIENT_QM_VISIT_ITEM\" WHERE ( ( \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"VISIT_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" AS \"VisitId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId13)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId14))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"QUALITY_MEASURE_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"QUALITY_MEASURE_ID\" AS \"QualityMeasureId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId15)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId16))))) AND \"PMDS\".\"PATIENT_QM_VISIT_ITEM\".\"QM_REPORTING_METHOD_ID\" IN (SELECT \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_REPORTING_METHOD_ID\" AS \"QmReportingMethodId\" FROM \"PMDS\".\"PATIENT_QM_VISIT\" WHERE ( ( ( ( \"PMDS\".\"PATIENT_QM_VISIT\".\"VISIT_ID\" = :VisitId17)) AND ( \"PMDS\".\"PATIENT_QM_VISIT\".\"QM_CLINICAL_STATUS_ID\" IN (:QmClinicalStatusId18)))))))))\r\n\tParameter: :VisitId1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId2 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId4 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId5 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId6 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId7 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId8 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId9 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId10 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId11 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId12 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId13 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId14 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId15 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId16 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n\tParameter: :VisitId17 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2485.\r\n\tParameter: :QmClinicalStatusId18 : Int16. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3.\r\n" StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchAdditionalPrefetchPath(IPrefetchPath2 prefetchPath, Context contextToUse, IEntity2 fetchedEntity, IRelationPredicateBucket filterToUse) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath) at ewave.PMDS.Server.DataAccess.LLBLGen.LLBLDataComponentGeneric`2.FetchEntity(EntityType entity, PrefetchPath2 prefetchPath) in C:\EwaveProjects\vs2008\Blumenthal\PMDS_EMR\Server\Resource Access\ewave.PMDS.Server.DataAccess\LLBLGen\LLBLDataComponentGeneric.cs:line 45 at ewave.PMDS.Server.DataAccess.DAVisit.FetchQMSummaryForVisit(Int64 visitId, QMClinicalStatus[] statusToGet, Int64 practiceId) in C:\EwaveProjects\vs2008\Blumenthal\PMDS_EMR\Server\Resource Access\ewave.PMDS.Server.DataAccess\DAVisit.cs:line 267 at ewave.PMDS.Server.BusinessLogic.Components.BcReportingQm.GetQMSummaryForVisit(DCSummaryQMVisitRequest request) in C:\EwaveProjects\vs2008\Blumenthal\PMDS_EMR\Server\Business Logic\ewave.PMDS.Server.BusinessLogic\Components\BcReportingQm.cs:line 118 at ewave.PMDS.Server.BusinessLogic.WorkFlow.WfReportingQm.GetQMSummaryForVisit(DCSummaryQMVisitRequest request) in C:\EwaveProjects\vs2008\Blumenthal\PMDS_EMR\Server\Business Logic\ewave.PMDS.Server.BusinessLogic\WorkFlow\WfReportingQm.cs:line 65 at ewave.PMDS.Server.ServiceImplementation.ReportingQmService.GetQMSummaryForVisit(DCSummaryQMVisitRequest request) in C:\EwaveProjects\vs2008\Blumenthal\PMDS_EMR\Server\Service Interface\ewave.PMDS.Server.ServiceImplementation\ReportingQmService.cs:line 58 at SyncInvokeGetQMSummaryForVisit(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) InnerException: Oracle.DataAccess.Client.OracleException Message="ORA-01013: user requested cancel of current operation" Source="Oracle Data Provider for .NET" ErrorCode=-2147467259 DataSource="PMDS_EMR_LITE_TEST" Number=1013 Procedure="" StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) InnerException:

Generated Query (Which failed with LLBLGen, but run perfectly directly at DB)

SELECT "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."VISIT_ID" AS "VisitId", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."QUALITY_MEASURE_ID" AS "QualityMeasureId", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."ITEM_ID" AS "ItemId", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."ITEM_CODE_ID" AS "ItemCodeId", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."CODE" AS "Code", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."CODE_SYSTEM_ID" AS "CodeSystemId", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."EXCLUSION_MODIFIER_ID" AS "ExclusionModifierId", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."CREATE_DATE" AS "CreateDate", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."CREATE_BY" AS "CreateBy", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."UPDATE_DATE" AS "UpdateDate", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."UPDATE_BY" AS "UpdateBy", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."TRANSACTION_ID" AS "TransactionId", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."CODE_DESCRIPTION" AS "CodeDescription", "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."PRACTICE_ID" AS "PracticeId" FROM "PMDS"."PATIENT_QM_VISIT_ITEM_CODE" WHERE ("PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."VISIT_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT_ITEM"."VISIT_ID" AS "VisitId" FROM "PMDS"."PATIENT_QM_VISIT_ITEM" WHERE (("PMDS"."PATIENT_QM_VISIT_ITEM"."VISIT_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."VISIT_ID" AS "VisitId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM"."QUALITY_MEASURE_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."QUALITY_MEASURE_ID" AS "QualityMeasureId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM"."QM_REPORTING_METHOD_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."QM_REPORTING_METHOD_ID" AS "QmReportingMethodId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3)))))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."QUALITY_MEASURE_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT_ITEM"."QUALITY_MEASURE_ID" AS "QualityMeasureId" FROM "PMDS"."PATIENT_QM_VISIT_ITEM" WHERE (("PMDS"."PATIENT_QM_VISIT_ITEM"."VISIT_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."VISIT_ID" AS "VisitId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM"."QUALITY_MEASURE_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."QUALITY_MEASURE_ID" AS "QualityMeasureId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM"."QM_REPORTING_METHOD_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."QM_REPORTING_METHOD_ID" AS "QmReportingMethodId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3)))))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM_CODE"."ITEM_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT_ITEM"."ITEM_ID" AS "ItemId" FROM "PMDS"."PATIENT_QM_VISIT_ITEM" WHERE (("PMDS"."PATIENT_QM_VISIT_ITEM"."VISIT_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."VISIT_ID" AS "VisitId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM"."QUALITY_MEASURE_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."QUALITY_MEASURE_ID" AS "QualityMeasureId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3))))) AND "PMDS"."PATIENT_QM_VISIT_ITEM"."QM_REPORTING_METHOD_ID" IN (SELECT "PMDS"."PATIENT_QM_VISIT"."QM_REPORTING_METHOD_ID" AS "QmReportingMethodId" FROM "PMDS"."PATIENT_QM_VISIT" WHERE (((("PMDS"."PATIENT_QM_VISIT"."VISIT_ID" = 2485)) AND ("PMDS"."PATIENT_QM_VISIT"."QM_CLINICAL_STATUS_ID" IN (3)))))))))

In almost all cases, the cause of this is a command timeout: the OracleCommand's timeout is reached before the full results are received and it closes the connection.

As you can see, the exception occurs inside the execution routine which simply calls the execute method on the oraclecommand to retrieve a datareader. This apparently doesn't succeed within the timeout period.

You can set the command timeout to a higher value by setting the property on the DataAccessAdapter instance used to a high value, e.g. 200 (seconds).

The query execution might be fast, though the whole resultset might be big with lots of blobs/clobs being returned over the network through the datareader, which might be slow and causing the timeout. Looking at the complexity of the query, I can imagine it takes some time to complete.

Hi, Otis. We already had the same problem, which was solved by your developers, because it was LLBLGen bug, and you sent us a new dll. We afraid to use this dll this time because it's too old.

Here is your, Otis, answer from the previous case:

(edit) found it!

Stay tuned, it's a little bug in the runtime lib.

(edit) Fixed it. See the attached DLL for the fix.

The issue was only in Adapter and was caused due to the removal of a second code path for the case where 0 relations were passed to an entity collection fetch. The single codepath which was left was refactored to take both cases, however we forgot to port an optimization for the special case where there were no relations present: in that case there can't be any duplicates in the resultset as entities are fetched, so DISTINCT isn't necessary .

Reference to the previous case : [http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14074&HighLight=1](http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14074&HighLight=1)

Svetlana wrote:

Hi, Otis. We already had the same problem, which was solved by your developers, because it was LLBLGen bug, and you sent us a new dll. We afraid to use this dll this time because it's too old.

All newer dlls have the bugfix, so that's why I don't think it's the same issue, as you're using the latest v2.6 build.

The problem in the linked thread was a timeout issue, as the query took forever to complete, and this was due to the 'DISTINCT' keyword. This DISTINCT keyword was unnecessary in the query and was accidently emitted by our code: the bugfix fixed that and didn't emit the DISTINCT keyword in the query anymore.

In your query, there's no DISTINCT, so the bugfix you relate to is already doing its job and it's not the same issue this time.

Please understand how this works: - you start a query - OracleCommand.ExecuteReader is called - this routine starts a timer (using the timeout specified) - the query doesn't complete in time, so the timer ends and oraclecommand closes the connection.

If this is a situation you can reproduce every time, please set the timeout to a high value and try again. As you experience this with prefetch paths, another way to make this faster (the issue is with the nested subqueries in the where clause) is by setting the ParameterizedPrefetchPathThreshold to a value higher than the # of elements fetched in the parent set (estimated). Not too high of course, but it's defaulted to 50, so if you fetch, say 200 parents in a parent-child path node, you should set it to 200 or higher to get a different query which uses an IN clause with constants instead of a subquery.

What I find strange as well, is that this error occurs when fetching a single entity with a prefetch path (see stacktrace) while you mention 20-30 entities being returned. ? (your code also doesn't show the exact fetch call)

Thanks, it works with ParameterizedPrefetchPathThreshold = 200.