I am trying to execute a rather complex query and I'm wondering if this is possible to do within LLBLGen v3 (Adapter usage)

This is the query that I'd like to run:

SELECT ISNULL(Comp.CompanyName, '**Private**') AS [Company], ISNULL(Cont.[Description], '---') AS [Contract],
    Cont.[ValidFromDate], Cont.[ValidToDate] , Cont.[ValueTotal] ,
    [Count], [Not Started], [In-Progress], [Accepted], [Declined]
FROM
    ((select Company_ID, Contract_ID, COUNT(*) AS [Count], SUM([Not Started]) AS 'Not Started', SUM([In-Progress]) AS 'In-Progress', 
        SUM([Accepted]) AS 'Accepted', SUM([Declined]) AS 'Declined'
        FROM (select Company_ID, Contract_ID, 
                CASE c_status WHEN 1 THEN 1 ELSE 0 END AS 'Not Started',
                CASE c_status WHEN 2 THEN 1 ELSE 0 END AS 'In-Progress',
                CASE c_status WHEN 3 THEN 1 ELSE 0 END AS 'Accepted',
                CASE c_status WHEN 4 THEN 1 ELSE 0 END AS 'Declined'
            from MKT_Prospect) Prospects
        GROUP BY Company_ID, Contract_ID) Prospects
    LEFT OUTER JOIN Company Comp ON Comp.Company_ID = Prospects.Company_ID)
    LEFT OUTER JOIN MKT_Contract Cont ON Cont.Contract_ID = Prospects.Contract_ID

I've managed to code the inner most query using llblgen as follows:

(Query)

select Company_ID, Contract_ID, 
    CASE c_status WHEN 1 THEN 1 ELSE 0 END AS 'Not Started',
    CASE c_status WHEN 2 THEN 1 ELSE 0 END AS 'In-Progress',
    CASE c_status WHEN 3 THEN 1 ELSE 0 END AS 'Accepted',
    CASE c_status WHEN 4 THEN 1 ELSE 0 END AS 'Declined'
from MKT_Prospect

(C# code)

var fields = new ResultsetFields(6);
fields.DefineField(MKT_ProspectFields.Company_ID, 0);
fields.DefineField(MKT_ProspectFields.Contract_ID, 1);
            
var fieldNotStarted = new EntityField2(CStatus.NOT_STARTED, new DbFunctionCall(
                                                            "CASE {0} WHEN 1 THEN 1 ELSE 0 END",
                                                              new object[] {MKT_ProspectFields.C_Status}));
var fieldInProgress = new EntityField2(CStatus.IN_PROGRESS, new DbFunctionCall(
                                                              "CASE {0} WHEN 2 THEN 1 ELSE 0 END",
                                                              new object[] {MKT_ProspectFields.C_Status}));
var fieldInAccepted = new EntityField2(CStatus.ACCEPTED, new DbFunctionCall(
                                                              "CASE {0} WHEN 3 THEN 1 ELSE 0 END",
                                                              new object[] {MKT_ProspectFields.C_Status}));
var fieldDeclined = new EntityField2(CStatus.DECLINED, new DbFunctionCall(
                                                              "CASE {0} WHEN 4 THEN 1 ELSE 0 END",
                                                              new object[] {MKT_ProspectFields.C_Status}));
    
fields.DefineField(fieldNotStarted, 2);
fields.DefineField(fieldInProgress, 3);
fields.DefineField(fieldInAccepted, 4);
fields.DefineField(fieldDeclined, 5);

var bucket= new RelationPredicateBucket();
var results = new DataTable();
adapter.FetchTypedList(fields, results, bucket);

Which evaluates to exactly the same sql as the original innermost select query.

Now that I've gotten this dynamic list, how do I use that as the "from" component for a new dynamic list using llblgen?

Any pointers would be greatly appreciated ...

Please check: Derived tables and dynamic relations

I think that I have made some progress towards my goal, however both of the examples in the linked documentation are for all fields of Entities / Entity collections

The end result of my query is not an entity but rather a dynamic list itself.

I have managed to construct the innermost and inner queries, its the final 'outer' part that I'm having trouble constructing:

SELECT ISNULL(Comp.CompanyName, '**Private**') AS [Company], ISNULL(Cont.[Description], '---') AS [Contract],
    Cont.[ValidFromDate], Cont.[ValidToDate] , Cont.[ValueTotal] ,
    [Count], [Not Started], [In-Progress], [Accepted], [Declined]
From (innerquery)
LEFT OUTER JOIN MKT_Contract Cont ON Cont.Contract_ID = Prospects.Contract_ID

It looks as if you can just pass in a RelationPredicateBucket with the dynamic relations attached to the Adapter.FetchTypedList method ...

But the dynamic relation takes a list of fields in its constructor as well as the FetchTyped list method which expects another list of fields.

Is there a way to just do a "Select *" for the typed list fetching into a dynamic table? Or do you need to redefine a whole new fields list using an object alias redefining everything from inside the inner query?

Could you please post your code which produced these queries?

Here is the code that I have written to perform the query above:

private DataTable FetchResults(bool UseProspectAddedDateFilter, bool UseContractExpireDateFilter, bool ValidContractsOnly, DateTime dateFrom, DateTime dateTo)
{
    const string ALIAS_INNERMOST = "CST";
    const string ALIAS_INNER = "Prs";
    const string ALIAS_FINAL = "Ovr";
    
    const string FIELD_COUNT = "Count";
    
    var innermostFields = new ResultsetFields(6);
    innermostFields.DefineField(MKT_ProspectFields.Company_ID, 0);
    innermostFields.DefineField(MKT_ProspectFields.Contract_ID, 1);
    
    var fieldNotStarted = new EntityField2(CStatus.NOT_STARTED, new DbFunctionCall(
                                                        "CASE {0} WHEN 1 THEN 1 ELSE 0 END",
                                                        new object[] {MKT_ProspectFields.C_Status}));
    var fieldInProgress = new EntityField2(CStatus.IN_PROGRESS, new DbFunctionCall(
                                                        "CASE {0} WHEN 2 THEN 1 ELSE 0 END",
                                                        new object[] {MKT_ProspectFields.C_Status}));
    var fieldInAccepted = new EntityField2(CStatus.ACCEPTED, new DbFunctionCall(
                                                        "CASE {0} WHEN 3 THEN 1 ELSE 0 END",
                                                        new object[] {MKT_ProspectFields.C_Status}));
    var fieldDeclined = new EntityField2(CStatus.DECLINED, new DbFunctionCall(
                                                        "CASE {0} WHEN 4 THEN 1 ELSE 0 END",
                                                        new object[] {MKT_ProspectFields.C_Status}));

    innermostFields.DefineField(fieldNotStarted, 2);
    innermostFields.DefineField(fieldInProgress, 3);
    innermostFields.DefineField(fieldInAccepted, 4);
    innermostFields.DefineField(fieldDeclined, 5);

    var innermostTable = new DerivedTableDefinition(innermostFields, ALIAS_INNERMOST);
    if (UseProspectAddedDateFilter)
    {
        innermostTable.Filter =
        new PredicateExpression(MKT_ProspectFields.DateCreated >= dateFrom & MKT_ProspectFields.DateCreated <= dateTo);
    }
    var innermostRelation = new DynamicRelation(innermostTable);

    var innerFields = new ResultsetFields(7);
    innerFields.DefineField(MKT_ProspectFields.Company_ID.SetObjectAlias(ALIAS_INNERMOST), 0);
    innerFields.DefineField(MKT_ProspectFields.Contract_ID.SetObjectAlias(ALIAS_INNERMOST), 1);
    innerFields.DefineField(new EntityField2(FIELD_COUNT, null, AggregateFunction.CountRow), 2);

    innerFields.DefineField(new EntityField2(CStatus.NOT_STARTED, null, AggregateFunction.Sum) { ObjectAlias = ALIAS_INNERMOST }, 3);
    innerFields.DefineField(new EntityField2(CStatus.IN_PROGRESS, null, AggregateFunction.Sum) { ObjectAlias = ALIAS_INNERMOST }, 4);
    innerFields.DefineField(new EntityField2(CStatus.ACCEPTED, null, AggregateFunction.Sum) { ObjectAlias = ALIAS_INNERMOST }, 5);
    innerFields.DefineField(new EntityField2(CStatus.DECLINED, null, AggregateFunction.Sum) { ObjectAlias = ALIAS_INNERMOST }, 6);

    var innerTable = new DerivedTableDefinition(innerFields, ALIAS_INNER, null, new RelationCollection(innermostRelation));
    innerTable.GroupBy = new GroupByCollection { innerFields[0], innerFields[1] };
    
    // JOIN onto Company table
    const string ALIAS_COMPANY = "Com";
    var withCompanyRelation = new DynamicRelation(innerTable, JoinHint.Left, EntityType.CompanyEntity, ALIAS_COMPANY,
                (MKT_ProspectFields.Company_ID.SetObjectAlias(ALIAS_INNER) == CompanyFields.Company_ID.SetObjectAlias(ALIAS_COMPANY)));

    var withCompanyFields = new ResultsetFields(8);
    withCompanyFields.DefineField(MKT_ProspectFields.Company_ID.SetObjectAlias(ALIAS_INNER), 0);
    withCompanyFields.DefineField(MKT_ProspectFields.Contract_ID.SetObjectAlias(ALIAS_INNER), 1);
    withCompanyFields.DefineField(CompanyFields.CompanyName.SetObjectAlias(ALIAS_COMPANY), 2);
    withCompanyFields.DefineField(new EntityField2(FIELD_COUNT, null) { ObjectAlias = ALIAS_INNER }, 3);
    withCompanyFields.DefineField(new EntityField2(CStatus.NOT_STARTED, null) { ObjectAlias = ALIAS_INNER }, 4);
    withCompanyFields.DefineField(new EntityField2(CStatus.IN_PROGRESS, null) { ObjectAlias = ALIAS_INNER }, 5);
    withCompanyFields.DefineField(new EntityField2(CStatus.ACCEPTED, null) { ObjectAlias = ALIAS_INNER }, 6);
    withCompanyFields.DefineField(new EntityField2(CStatus.DECLINED, null) { ObjectAlias = ALIAS_INNER }, 7);

    var withCompanyTable = new DerivedTableDefinition(withCompanyFields, ALIAS_FINAL, null, new RelationCollection(withCompanyRelation));

    // JOIN onto the contract table
    const string ALIAS_CONTRACT = "Con";
    var outerRelation = new DynamicRelation(withCompanyTable, JoinHint.Left, EntityType.MKT_ContractEntity, ALIAS_CONTRACT,
    (MKT_ContractFields.Contract_ID.SetObjectAlias(ALIAS_CONTRACT) ==
     MKT_ProspectFields.Contract_ID.SetObjectAlias(ALIAS_FINAL)));
    
    // Outermost fields list
    var outerFields = new ResultsetFields(12);
    outerFields.DefineField(MKT_ProspectFields.Company_ID.SetObjectAlias(ALIAS_FINAL), 0);
    outerFields.DefineField(MKT_ProspectFields.Contract_ID.SetObjectAlias(ALIAS_FINAL), 1);
    outerFields.DefineField(CompanyFields.CompanyName.SetObjectAlias(ALIAS_FINAL), 2);
    outerFields.DefineField(MKT_ContractFields.Description.SetObjectAlias(ALIAS_CONTRACT), 3);
    outerFields.DefineField(MKT_ContractFields.ValidFromDate.SetObjectAlias(ALIAS_CONTRACT), 4);
    outerFields.DefineField(MKT_ContractFields.ValidToDate.SetObjectAlias(ALIAS_CONTRACT), 5);
    outerFields.DefineField(MKT_ContractFields.ValueTotal.SetObjectAlias(ALIAS_CONTRACT), 6);
    outerFields.DefineField(new EntityField2(FIELD_COUNT, null) { ObjectAlias = ALIAS_FINAL }, 7);
    outerFields.DefineField(new EntityField2(CStatus.NOT_STARTED, null) { ObjectAlias = ALIAS_FINAL }, 8);
    outerFields.DefineField(new EntityField2(CStatus.IN_PROGRESS, null) { ObjectAlias = ALIAS_FINAL }, 9);
    outerFields.DefineField(new EntityField2(CStatus.ACCEPTED, null) { ObjectAlias = ALIAS_FINAL }, 10);
    outerFields.DefineField(new EntityField2(CStatus.DECLINED, null) { ObjectAlias = ALIAS_FINAL }, 11);

    var outerTable = new DerivedTableDefinition(outerFields, ALIAS_FINAL, null, new RelationCollection(outerRelation));

    // Finalise
    var finalTable = outerTable;
    var finalRelation = new DynamicRelation(finalTable);
    var filter = new RelationPredicateBucket();
    filter.Relations.Add(finalRelation);
    var results = new DataTable();

    var finalFields = GetFinalFields(outerFields, finalTable.Alias);
    adapter.FetchTypedList(finalFields, results, filter);
    return results;
}

private ResultsetFields GetFinalFields(ResultsetFields fieldsToCopy, string aliasToSet)
{
    var finalFields = new ResultsetFields(fieldsToCopy.Count);
    for (int i = 0; i < fieldsToCopy.Count; i++)
    {
        var field = fieldsToCopy[i];
        finalFields.DefineField(new EntityField2(field.Name, aliasToSet, field.DataType), i);
    }
    return finalFields;
}

Here is the resultant SQL output

SELECT [LPA_O1].[Company_ID],
       [LPA_O1].[Contract_ID],
       [LPA_O1].[CompanyName],
       [LPA_O1].[Description],
       [LPA_O1].[ValidFromDate],
       [LPA_O1].[ValidToDate],
       [LPA_O1].[ValueTotal],
       [LPA_O1].[Count],
       [LPA_O1].[Not Started],
       [LPA_O1].[In-Progress],
       [LPA_O1].[Accepted],
       [LPA_O1].[Declined]
FROM   (SELECT [LPA_O2].[Company_ID],
               [LPA_O2].[Contract_ID],
               [LPA_O2].[CompanyName],
               [LPA_C3].[Description],
               [LPA_C3].[ValidFromDate],
               [LPA_C3].[ValidToDate],
               [LPA_C3].[ValueTotal],
               [LPA_O2].[Count],
               [LPA_O2].[Not Started],
               [LPA_O2].[In-Progress],
               [LPA_O2].[Accepted],
               [LPA_O2].[Declined]
        FROM   ( (SELECT [LPA_P4].[Company_ID],
                       [LPA_P4].[Contract_ID],
                       [LPA_C5].[companyName] AS [CompanyName],
                       [LPA_P4].[Count],
                       [LPA_P4].[Not Started],
                       [LPA_P4].[In-Progress],
                       [LPA_P4].[Accepted],
                       [LPA_P4].[Declined]
                FROM   ( (SELECT [LPA_C6].[Company_ID],
                               [LPA_C6].[Contract_ID],
                               COUNT(*)                 AS [Count],
                               SUM([LPA_C6].[Not Started]) AS [Not Started],
                               SUM([LPA_C6].[In-Progress]) AS [In-Progress],
                               SUM([LPA_C6].[Accepted]) AS [Accepted],
                               SUM([LPA_C6].[Declined]) AS [Declined]
                        FROM   (SELECT [dbo].[MKT_Prospect].[Company_ID],
                                       [dbo].[MKT_Prospect].[Contract_ID],
                                       CASE [dbo].[MKT_Prospect].[C_Status]
                                         WHEN 1 THEN 1
                                         ELSE 0
                                       END AS [Not Started],
                                       CASE [dbo].[MKT_Prospect].[C_Status]
                                         WHEN 2 THEN 1
                                         ELSE 0
                                       END AS [In-Progress],
                                       CASE [dbo].[MKT_Prospect].[C_Status]
                                         WHEN 3 THEN 1
                                         ELSE 0
                                       END AS [Accepted],
                                       CASE [dbo].[MKT_Prospect].[C_Status]
                                         WHEN 4 THEN 1
                                         ELSE 0
                                       END AS [Declined]
                                FROM   [dbo].[MKT_Prospect]) [LPA_C6]
                        GROUP  BY [LPA_C6].[Company_ID],
                                  [LPA_C6].[Contract_ID]) [LPA_P4]
                         LEFT JOIN [dbo].[Company] [LPA_C5]
                           ON [LPA_P4].[Company_ID] = [LPA_C5].[company_ID]))
               [LPA_O2]
                 LEFT JOIN [dbo].[MKT_Contract] [LPA_C3]
                   ON [LPA_C3].[Contract_ID] = [LPA_O2].[Contract_ID])) [LPA_O1]  

With the above code posted, I'll rephrase my previous question as follows:

I have the final outerRelation and outerFields. In order to fetch this data I then create a outerTable (using the final outerRelation and outerFields).

Now, as far as I can tell, in order to fetch that outerTable, I then need to:

• Create a DynamicRelation using that outertable
• Create a RelationPredicateBucket using the created DynamicRelation
• Create a new set of fields that is exactly the same as the inner table's, setting the ObjectAlias for all of the fields to the alias of the finalTable (outerTable in this case)

Which results in the output sql query selecting into the inner query all of its results.

Now that will get me what I want, however, I was wondering if either:

• Can You fetch in another method to avoid having to do what is mentioned above?
• Failing that, somehow do a "Select *" for a FetchTypedList?

Finally, some other questions that arose while creating this query:

• Is it possible to do multiple left joins at the same level? (In the above example, left join onto Company AND Contract in one operation rather than nesting as I have done)
• For future reference, is it possible to tell LLBLGen to name its queries with the alias name that you specify in code? (rather than getting LPA_P4 etc in the output SQL)
• Am I right in considering this type of query not possible to be done using the Linq to LLBLGenPro?

Waw this is a huge pile of code to look at.

I'm sorry but could you please post a repro code of just one level of nesting, and please limit the code to very few lines which can reproduce the problem. Remove any extra fields, filters...etc.