Been using LLBLGEN Pro for a little over a year (just fresh out of college) and have really enjoyed it. Recently I created a survey system for our company. One of the goals of the survey's was to allow for easy creation of new survey's just by creating a new object (no need to create a new database every time). So long story short I created a couple of tables to handle an infinite amount of surveys.
The schema works pretty good. Now I'm trying to make an easy process to query these surveys.

The main table I'm working with right now is SurveyResponse, which has foreign keys to SurveyCompleted and SurveyQuestion.

What I'm trying to accomplish can be done with the following SQL statement:

select * 
from survey_completed s
where 'nada' in (select response from survey_response where survey_completed_id = s.SURVEY_COMPLETED_ID and survey_question_id = 'COMMENTS' )
and 'No' in (select response from survey_response where survey_completed_id = s.SURVEY_COMPLETED_ID and survey_question_id = 'WEBSITE EASY TO NAVIGATE/UNDERSTAND' );

So the SQL statement will only return the completed survey if the Responses contains the question I'm looking for and also the correct response to that question, Note I add an And in the where clause but Or can also be accepted.

I started using Derived Tables and Dynamic relations but I'm not sure if that is really what I need, and if it is I'm doing it wrong. When I run the following code it throws an exception and when I look at the query it is trying to execute it doesn't look like the data I need.

// first specify the elements in the derived table select (which is a dyn. list)
            ResultsetFields dtFields = new ResultsetFields(3);
            dtFields.DefineField(IntlSurveyResponseFields.SurveyCompletedId, 0);
            dtFields.DefineField(IntlSurveyResponseFields.SurveyQuestionId, 1);
            dtFields.DefineField(IntlSurveyResponseFields.Response, 2);
            IPredicateExpression predicateExpression = new PredicateExpression(IntlSurveyResponseFields.SurveyQuestionId == "COMMENTS");
            predicateExpression.AddWithAnd(IntlSurveyResponseFields.Response == "foobar");
            DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "CompletedSurvey", predicateExpression);

            // then specify the relation. 
            // derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
            DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner, EntityType.IntlSurveyResponseEntity, "C",
            new EntityField(IntlSurveyResponseFields.SurveyCompletedId.ToString(), "CompletedSurvey", typeof(decimal)) == IntlSurveyCompletedFields.SurveyCompletedId.SetObjectAlias("C"));
            
            // then specify the rest of the query elements
            RelationCollection relations = new RelationCollection();
            relations.Add(relation);
            relations.SelectListAlias = "C";

            // then fetch the data
            IntlSurveyCompletedCollection orders = new IntlSurveyCompletedCollection();
            orders.GetMulti(null, relations);

I can get this to work with Linq to LLBLGEN Pro however I use some anonymous types and I would like to be able to add prefetched paths when I do the final query. Any help would be greatly appreciated, if I'm approaching it all wrong I apologize in advance.

Edit I'm using LLBLGen Pro v4.2 and .NET 4.5

Exception Thrown

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException was unhandled
  HResult=-2146232832
  Message=An exception was caught during the execution of a retrieval query: ORA-00904: "C"."SURVEY_COMPLETED_ID": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  Source=SD.LLBLGen.Pro.ORMSupportClasses
  RuntimeBuild=08112014
  RuntimeVersion=4.2.0.0
  QueryExecuted=
    Query: SELECT DISTINCT "C"."SURVEY_COMPLETED_ID" AS "SurveyCompletedId", "C"."PARTICIPANT_ID" AS "ParticipantId", "C"."SURVEY_PERIOD_ID" AS "SurveyPeriodId", "C"."COMPLETED_DATE" AS "CompletedDate", "C"."VERSION" AS "Version" FROM ( (SELECT "INTL"."SURVEY_RESPONSE"."SURVEY_COMPLETED_ID" AS "SurveyCompletedId", "INTL"."SURVEY_RESPONSE"."SURVEY_QUESTION_ID" AS "SurveyQuestionId", "INTL"."SURVEY_RESPONSE"."RESPONSE" AS "Response" FROM "INTL"."SURVEY_RESPONSE" WHERE ( "INTL"."SURVEY_RESPONSE"."SURVEY_QUESTION_ID" = :p1 AND "INTL"."SURVEY_RESPONSE"."RESPONSE" = :p2)) "LPA_C1"  INNER JOIN "INTL"."SURVEY_RESPONSE" "LPA_C2"  ON  "LPA_C1"."F__-1438246346" = "C"."SURVEY_COMPLETED_ID")
    Parameter: :p1 : String. Length: 512. Precision: 0. Scale: 0. Direction: Input. Value: "COMMENTS".
    Parameter: :p2 : String. Length: 2000. Precision: 0. Scale: 0. Direction: Input. Value: "foobar".

  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String& failureErrorText)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, ITransaction containingTransaction, IEntityCollection collectionToFill, Boolean allowDuplicates, IEntityFields fieldsUsedForQuery, IFieldPersistenceInfo[] fieldPersistenceInfos)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAction(ITransaction containingTransaction, QueryParameters parameters)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.GetMulti(ITransaction containingTransaction, IEntityFactory entityFactoryToUse, QueryParameters parameters)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.PerformGetMulti(QueryParameters parameters)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relations, IPrefetchPath prefetchPathToUse, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.ORMSupportClasses.EntityCollectionBase`1.GetMulti(IPredicate selectFilter, IRelationCollection relations)
       at DMBA.Data.Oracle.DMBAHelperClasses.International.Survey.Surveys.IntlSurveyCollection`1.GetPredicate() in c:\Workspace\ORM\Trunk\Oracle\survey\code\DMBAHelperClasses\International\Survey\IntlSurveyCollection.cs:line 87
       at DMBA.Data.Oracle.DMBAHelperClasses.International.Survey.Surveys.IntlSurveyCollection`1.GetSurveyCollection(Expression`1 expression) in c:\Workspace\ORM\Trunk\Oracle\survey\code\DMBAHelperClasses\International\Survey\IntlSurveyCollection.cs:line 36
       at IntlUtility.Program.Main() in c:\Workspace\Console Apps\IntlUtility\Program.cs:line 14
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: Oracle.DataAccess.Client.OracleException
       HResult=-2147467259
       Message=ORA-00904: "C"."SURVEY_COMPLETED_ID": invalid identifier
       Source=Oracle Data Provider for .NET
       ErrorCode=-2147467259
       DataSource=DMTST_SYS3
       Number=904
       Procedure=""
       StackTrace:
            at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
            at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
            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.ExecuteReader(CommandBehavior behavior)
            at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       InnerException: 

Yep, I was way off, I discovered FieldCompareSetPredicate(s) and that solved my problem.

Instead of:

DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner, EntityType.IntlSurveyResponseEntity, "C", new EntityField(IntlSurveyResponseFields.SurveyCompletedId.ToString(), "CompletedSurvey", typeof(decimal)) == IntlSurveyCompletedFields.SurveyCompletedId.SetObjectAlias("C"));

You need to do this:

 DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner, EntityType.IntlSurveyCompletedEntity, "C",
            new EntityField(IntlSurveyResponseFields.SurveyCompletedId.ToString(), "CompletedSurvey", typeof(decimal)) == IntlSurveyCompletedFields.SurveyCompletedId.SetObjectAlias("C"));