I'm using LLBL 2.6 final in self-servicing mode accessing a sql server 2008 database. Below is the original query I wrote and am trying to convert to LLBL.

select UniqueSearchID
from (
        select isr.UniqueSearchID,
        sum(summ.ResultCount) as summaryCount,
        COALESCE(sum(dtl.ResultCount), 0) as detailCount
        from ProductSearchResponse isr
        left outer join ProductSearchResponse summ on isr.UniqueSearchID = summ.UniqueSearchID and summ.LastTypeRecd = 'S'
        left outer join ProductSearchResponse dtl on summ.UniqueSearchID = dtl.UniqueSearchID and dtl.LastTypeRecd = 'D'
        where isr.LastTypeRecd = 'R'
        group by isr.UniqueSearchID 
) as tbl
where tbl.summaryCount > tbl.detailCount

I believe I should be able to accomplish this using the DerivedTableDefinition that is explained in the documentation here: http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm

The problem is that both examples that are listed here are using a EntityCollection to retrieve the results, and they are using a DynamicRelation to join from the Entity to the derived table. However, in my case, I simply want to select out of the derived table.

I suppose first off, if you have a better suggestion to retrieve the same results, that's probably the best solution. Assuming that this query is "correct" - how can I select directly from the derived table without an entity collection?

You can accomplish that with DynamicList.

This is a rephrased version of your query:

SELECT 
     isr.UniqueSearchID,
     sum(summ.ResultCount) AS summaryCount,

     COALESCE(sum(dtl.ResultCount), 0) AS detailCount
    
FROM 
     ProductSearchResponse isr
    
     LEFT JOIN ProductSearchResponse summ on isr.UniqueSearchID = summ.UniqueSearchID AND summ.LastTypeRecd = 'S'
        
     LEFT JOIN ProductSearchResponse dtl on summ.UniqueSearchID = dtl.UniqueSearchID AND dtl.LastTypeRecd = 'D'
        
WHERE 
     isr.LastTypeRecd = 'R'
        
GROUP BY 
     isr.UniqueSearchID 

HAVING
     tbl.summaryCount > tbl.detailCount

Some tips:

To reproduce the follow sql you will need to use DBFunctionCalls

COALESCE(sum(dtl.ResultCount), 0) AS detailCount

For the joins, you will need to use Custom filter. You will also need to alias the relations.

LEFT JOIN ProductSearchResponse summ on isr.UniqueSearchID = summ.UniqueSearchID AND summ.LastTypeRecd = 'S'
        
     LEFT JOIN ProductSearchResponse dtl on summ.UniqueSearchID = dtl.UniqueSearchID AND dtl.LastTypeRecd = 'D'

Give it a try. Please come back here if you need further assistance.

Great, thanks for the guidance. As always, the support here is awesome - keep it up!

Ok, so I've been working on this and have not been able to get the query to work as expected. Well, more exactly, it complains during the verification. Below is my attempt to convert your provided query into LLBL request:

            var fields = new ResultsetFields(3);
            fields.DefineField(RequestedProductFields.ProductRequestId, 0, "requestId", "isr");
            fields.DefineField(ProductSearchResponseFields.ResultCount, 1, "summaryCount", "summ", AggregateFunction.Sum);
            fields.DefineField(ProductSearchResponseFields.ResultCount, 2, "detailCount", "dtl", AggregateFunction.Sum);
            fields[2].ExpressionToApply = new DbFunctionCall("COALESCE({0}, 0)", new object[] {ProductSearchResponseFields.ResultCount});

            var summaryJoin = new EntityRelation(ProductSearchResponseFields.UniqueSearchId, ProductSearchResponseFields.UniqueSearchId, RelationType.OneToMany);
            summaryJoin.SetAliases("isr", "summ");
            summaryJoin.CustomFilter = new PredicateExpression(ProductSearchResponseFields.LastTypeRecd == "S");

            var detailJoin = new EntityRelation(ProductSearchResponseFields.UniqueSearchId, ProductSearchResponseFields.UniqueSearchId, RelationType.OneToMany);
            detailJoin.SetAliases("dtl", "summ");
            detailJoin.CustomFilter = new PredicateExpression(ProductSearchResponseFields.LastTypeRecd == "D");

            var reqProdJoin = new EntityRelation(RequestedProductFields.RequestedProductId, ProductSearchResponseFields.RequestedProductId, RelationType.OneToMany);
            reqProdJoin.SetAliases("req", "isr");

            var joins = new RelationCollection();
            joins.Add(summaryJoin);
            joins.Add(detailJoin);
            joins.Add(reqProdJoin);

            var criteria = new PredicateExpression(ProductSearchResponseFields.LastTypeRecd == "R");
            criteria.ObjectAlias = "isr";

            var groupBy = new GroupByCollection();
            groupBy.Add(fields[1]);
            groupBy.Add(fields[2]);
            groupBy.HavingClause = new PredicateExpression(fields[1].SetAggregateFunction(AggregateFunction.Sum) > fields[2].SetExpression(fields[2].ExpressionToApply));

            var dt = new DataTable();
            var dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dt, 0, null, criteria, joins, false, groupBy, null, 0, 0);

On the the last line that actually tries to execute, I received back the following exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException was unhandled
  Message="Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?"
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  RuntimeBuild="10132008"
  RuntimeVersion="2.6.0.0"

The full stack trace was rather large, but if you need it, I'll be happy to supply. I think the problem is that I'm unable to get the alias of "isr" on the table listed in the from clause directly rather than a table on a join.

Any help would be much appreciated - again!

Thanks, Matt

Your code doesn't match the SQL posted earlier, so one of them must be wrong.

from ProductSearchResponse isr left outer join ProductSearchResponse left outer join ProductSearchResponse

var fields = new ResultsetFields(3); fields.DefineField(RequestedProductFields.ProductRequestId, 0, "requestId", "isr"); fields.DefineField(ProductSearchResponseFields.ResultCount, 1, "summaryCount", "summ", AggregateFunction.Sum); fields.DefineField(ProductSearchResponseFields.ResultCount, 2, "detailCount", "dtl", AggregateFunction.Sum);

So what's RequestedProduct doing here ?

I'm very sorry about that, I did add another join and forgot about that change. Here is the slightly modified version.

SELECT
     req.ProductRequestID,
     sum(summ.ResultCount) AS summaryCount,
     COALESCE(sum(dtl.ResultCount), 0) AS detailCount
FROM
     ProductSearchResponse isr
     LEFT JOIN ProductSearchResponse summ on isr.UniqueSearchID = summ.UniqueSearchID AND summ.LastTypeRecd = 'S'
     LEFT JOIN ProductSearchResponse dtl on summ.UniqueSearchID = dtl.UniqueSearchID AND dtl.LastTypeRecd = 'D'
     LEFT JOIN RequestedProduct req on isr.RequestedProductID = req.RequestedProductID
WHERE
     isr.LastTypeRecd = 'R'
GROUP BY
     req.ProductRequestID
HAVING
    sum(summ.ResultCount) > COALESCE(sum(dtl.ResultCount), 0)

The only addition from what you provided is the last join to RequestedProduct, and I had to remove the table prefixes in the HAVING clause.

Again, I apologize for making a change "behind your back" and appreciate the help and support.

Matt

SELECT req.ProductRequestID, sum(summ.ResultCount) AS summaryCount, COALESCE(sum(dtl.ResultCount), 0) AS detailCount FROM ProductSearchResponse isr LEFT JOIN ProductSearchResponse summ on isr.UniqueSearchID = summ.UniqueSearchID AND summ.LastTypeRecd = 'S' LEFT JOIN ProductSearchResponse dtl on summ.UniqueSearchID = dtl.UniqueSearchID AND dtl.LastTypeRecd = 'D' LEFT JOIN RequestedProduct req on isr.RequestedProductID = req.RequestedProductID WHERE isr.LastTypeRecd = 'R' GROUP BY req.ProductRequestID HAVING sum(summ.ResultCount) > COALESCE(sum(dtl.ResultCount), 0)

The code should look like the following. (just out of my head, and not tested, but it should give you a good idea, of things you have been missing or mistaken about).

            var fields = new ResultsetFields(3);
            fields.DefineField(RequestedProductFields.ProductRequestId, 0, "requestId", "isr");
            fields.DefineField(ProductSearchResponseFields.ResultCount, 1, "summaryCount", "summ", AggregateFunction.Sum);
            fields.DefineField(ProductSearchResponseFields.ResultCount, 2, "detailCount");
            fields[2].ExpressionToApply = new DbFunctionCall("COALESCE(SUM({0}), 0)", new object[] {ProductSearchResponseFields.ResultCount.SetObjectAlias("dtl")});

            var summaryJoin = new EntityRelation(ProductSearchResponseFields.UniqueSearchId, ProductSearchResponseFields.UniqueSearchId, RelationType.OneToMany);
            summaryJoin.SetAliases("isr", "summ");
            summaryJoin.CustomFilter = new PredicateExpression(ProductSearchResponseFields.LastTypeRecd.SetObjectAlias("summ") == "S");

            var detailJoin = new EntityRelation(ProductSearchResponseFields.UniqueSearchId, ProductSearchResponseFields.UniqueSearchId, RelationType.OneToMany);
            detailJoin.SetAliases("dtl", "summ");
            detailJoin.CustomFilter = new PredicateExpression(ProductSearchResponseFields.LastTypeRecd.SetObjectAlias("dtl") == "D");

            var reqProdJoin = new EntityRelation(ProductSearchResponseFields.RequestedProductId, RequestedProductFields.RequestedProductId, RelationType.OneToMany);
            reqProdJoin.SetAliases("isr", "req");

            var joins = new RelationCollection();
            joins.Add(summaryJoin, JoinHint.Left);
            joins.Add(detailJoin, JoinHint.Left);
            joins.Add(reqProdJoin, JoinHint.Left);

            var criteria = new PredicateExpression(ProductSearchResponseFields.LastTypeRecd.SetObjectAlias("isr") == "R");

            var groupBy = new GroupByCollection();
            groupBy.Add(fields[1]);
            groupBy.Add(fields[2]);
            groupBy.HavingClause = new PredicateExpression(fields[0].SetAggregateFunction(AggregateFunction.Sum) > fields[2]);

            var dt = new DataTable();
            var dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dt, 0, null, criteria, joins, false, groupBy, null, 0, 0);

If this doesn't work, then please post any exception/error text, and post the generated SQL query.

When trying to execute the provided code, I receive the following exception, which I believe is the same as I was originally receiving:

SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException was unhandled
  Message="Relation at index 2 doesn't contain an entity already added to the FROM clause. Bad alias?"
  Source="SD.LLBLGen.Pro.ORMSupportClasses.NET20"
  RuntimeBuild="10132008"
  RuntimeVersion="2.6.0.0"
  StackTrace:
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PreprocessRelations()
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Int32& uniqueMarker, Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix)
       at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText(Int32& uniqueMarker)
       at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
       at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
       at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFields selectList, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.CreateQueryFromElements(ITransaction transactionToUse, IEntityFields fields, IPredicate filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
       at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize)

In this scenario, I wasn't able to retrieve the generated query. I commented out the line adding the join 'reqProdJoin' that was the cause of this error, and then I was able to have the query generate (though it obviously failed due to lack of joins as well). Here is the generated query minus that one join.

SELECT DISTINCT 
[isr].[ProductRequestID] AS [requestId], 
SUM([LPA_s1].[ResultCount]) AS [summaryCount], 
COALESCE(SUM([LPA_d3].[ResultCount]), 0) AS [detailCount] 
FROM 
(
    ( 
        [GWS].[dbo].[ProductSearchResponse] [LPA_s1]  
        RIGHT JOIN [GWS].[dbo].[ProductSearchResponse] [LPA_i2]  
            ON  [LPA_s1].[UniqueSearchID]=[LPA_i2].[UniqueSearchID] AND 
            [LPA_s1].[LastTypeRecd] = @LastTypeRecd1
    ) 
        RIGHT JOIN [GWS].[dbo].[ProductSearchResponse] [LPA_d3]  
            ON  [LPA_s1].[UniqueSearchID]=[LPA_d3].[UniqueSearchID] AND 
            [LPA_d3].[LastTypeRecd] = @LastTypeRecd2
) 
WHERE [LPA_i2].[LastTypeRecd] = @LastTypeRecd3
GROUP BY [LPA_s1].[ResultCount], 
COALESCE(SUM([LPA_d3].[ResultCount]), 0) 
HAVING ( SUM([LPA_s1].[ResultCount]) > COALESCE(SUM([LPA_d3].[ResultCount]), 0))

And the parameters output were:

    Parameter: @LastTypeRecd1 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "S".
    Parameter: @LastTypeRecd2 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "D".
    Parameter: @LastTypeRecd3 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "R".

Please post the generated code when you received the error.

Anyway, I think the problem is in the first field. It should be (check the alias):

fields.DefineField(RequestedProductFields.ProductRequestId, 0, "requestId", "req");

I changed the alias on the first field as you suggested and still received the same error regarding a bad alias in the FROM clause. This is the same stack that was posted in the message above.

I can't provide the generated query in this case because nothing was output. It seems this failure occurs during some pre-validation routine. The query I posted in my last message was the closest I could obtain and was generated by removing the 'reqProdJoin'.

I am wondering if I'm putting too much effort into this as I could put the same logic inside a view or stored procedure and not have to deal with trying to get LLBL to generate the query I need. In general, do you feel "complex" queries should be handled in this way, or is using the dynamic nature of LLBL supported in these scenarios?

Thanks for sticking with the problem however, your support is top notch!

Matt

The decision about where to put these complex queries is really down to you - LLBLGen should support them, but there is no denying that the syntax can sometimes make it complicated to construct these complex queries.

If you'd like us to carry on investigating, could you provide us with a repro solution that will allow us to fire up the code and run it against a db - this will allow us to debug it to see exactly where the issue is coming from.

Matt

I went ahead and added a view and TypedView this morning and that seems to work just great. I originally wanted to keep it in code just because we don't normally use many views and I wanted to be consistent.

Thanks again for trying to work through it, but I believe the typed view will work for me in this case.

Matt

No problem, we're always happy to help.

Matt