How to use ListAgg Oracle function ?

Posts   
1  /  2
 
    
Posts: 61
Joined: 14-Feb-2017
# Posted on: 06-Apr-2021 16:36:21   

Hi,

I have the following tables

OPERATION
       ID
       ....
  
TECHNICIEN
       ID
       NOM
  
TECHNICIEN_OPERATION
       ID_TECHNICIEN
       ID_OPERATION

I'm trying to use the ListAgg Oracle function to retrieve the list of operations with for each operation the techniciens linked to the operation separated with comma. Something like this

OPERATION            TECHNCIENS
          1                              James, Kevin
          2                              Roger, Georges

In SQL, the following query is what I want.

SELECT
  OPERATION.ID,
  (SELECT LISTAGG(TECHNICIEN.NOM, ', ') WITHIN GROUP (ORDER BY TECHNICIEN.NOM)
    FROM TECHNICIEN_OPERATION
    INNER JOIN TECHNICIEN ON TECHNICIEN.ID = TECHNICIEN_OPERATION.ID_TECHNICIEN
    WHERE TECHNICIEN_OPERATION.ID_OPERATION = OPERATION.ID
  ) AS CodesTechniciens
FROM OPERATION

Knowing I created the following function mapping

public class LinqDatabaseFunctionMappings : FunctionMappingStore
    {
        public LinqDatabaseFunctionMappings()
            : base()
        {
            this.Add(new FunctionMapping(typeof(LinqDatabaseFunctions), "ListAgg", 1, "LISTAGG({0}, ', ') WITHIN GROUP(ORDER BY {0})"));
        }
    }

   public static class LinqDatabaseFunctions
    {
        public static string ListAgg(string columnName)
        {
            return string.Empty;
        }
    }

How can I achieve this kind of query using LLBLGEN ? I don't really know where to place the LinqDatabaseFunctions.ListAgg() call.

                var query = from operation in LinqMetaData.Operation
                            select new OperationViewModel
                            {
                                Id = operation.Id,
                                Techniciens = (from technicienOperation in LinqMetaData.TechnicienOperation
                                                    join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                                    where technicienOperation.IdOperation == operation.Id
                                                    group technicien by technicien.Nom into g
                                                    select LinqDatabaseFunctions.ListAgg(g.Key)).FirstOrDefault(),
                            };
Attachments
Filename File size Added on Approval
LLBLGenTests.zip 33,737 09-Apr-2021 08:03.40 Approved
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 06-Apr-2021 21:00:42   

So what SQL did that code produce?

And what would the inner query alone produce?

from technicienOperation in LinqMetaData.TechnicienOperation
                                                    join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                                    where technicienOperation.IdOperation == operation.Id
                                                    group technicien by technicien.Nom into g
                                                    select LinqDatabaseFunctions.ListAgg(g. Key)
                   

Posts: 61
Joined: 14-Feb-2017
# Posted on: 07-Apr-2021 07:01:45   

Below you will find the generated queries I found on the log.

Query

SELECT "LPA_L4"."nom" AS "Nom"  
FROM   ("NEPTUNE_AEP"."technicien_operation" "LPA_L3"  
        INNER JOIN "NEPTUNE_AEP"."technicien" "LPA_L4"  
                ON "LPA_L3"."id_technicien" = "LPA_L4"."id")  
WHERE  ((( "LPA_L3"."id_operation" = "lpla_1"."ID" )))  
GROUP  BY "LPA_L4"."nom"  

Subquery

SELECT Listagg("LPA_L2"."nom", ', ')  
         within GROUP(ORDER BY "LPA_L2"."Nom") AS "LPFA_5"  
FROM   (SELECT "LPA_L4"."nom" AS "Nom"  
        FROM   ("NEPTUNE_AEP"."technicien_operation" "LPA_L3"  
                inner join "NEPTUNE_AEP"."technicien" "LPA_L4"  
                        ON "LPA_L3"."id_technicien" = "LPA_L4"."id")  
        WHERE  ((( "LPA_L3"."id_operation" = "lpla_1"."ID" )))  
        GROUP  BY "LPA_L4"."nom") "LPA_L2"  
WHERE  ROWNUM <= 1   

Query with subquery

SELECT "LPLA_1"."id"        AS "Id",  
       (SELECT Listagg("LPA_L2"."nom", ', ')  
                 within GROUP(ORDER BY "LPA_L2"."Nom") AS "LPFA_5"  
        FROM   (SELECT "LPA_L4"."nom" AS "Nom"  
                FROM   ("NEPTUNE_AEP"."technicien_operation" "LPA_L3"  
                        inner join "NEPTUNE_AEP"."technicien" "LPA_L4"  
                                ON "LPA_L3"."id_technicien" = "LPA_L4"."id")  
                WHERE  ((( "LPA_L3"."id_operation" = "LPLA_1"."id" )))  
                GROUP  BY "LPA_L4"."nom") "LPA_L2"  
        WHERE  ROWNUM <= 1) AS "Techniciens"  
FROM   "NEPTUNE_AEP"."operation" "LPLA_1" 

In fact, the subquery must : * search for the list of techniciens linked to an operation * aggregate this list to return only a string

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 08-Apr-2021 05:25:58   

I might be missing something, could you please point out to me the error in the following generated query?

SELECT "LPLA_1"."id"        AS "Id",  
       (SELECT Listagg("LPA_L2"."nom", ', ')  
                 within GROUP(ORDER BY "LPA_L2"."Nom") AS "LPFA_5"  
        FROM   (SELECT "LPA_L4"."nom" AS "Nom"  
                FROM   ("NEPTUNE_AEP"."technicien_operation" "LPA_L3"  
                        inner join "NEPTUNE_AEP"."technicien" "LPA_L4"  
                                ON "LPA_L3"."id_technicien" = "LPA_L4"."id")  
                WHERE  ((( "LPA_L3"."id_operation" = "LPLA_1"."id" )))  
                GROUP  BY "LPA_L4"."nom") "LPA_L2"  
        WHERE  ROWNUM <= 1) AS "Techniciens"  
FROM   "NEPTUNE_AEP"."operation" "LPLA_1" 
Posts: 61
Joined: 14-Feb-2017
# Posted on: 08-Apr-2021 09:47:11   

The query throws an exception

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ORA-00904: "LPA_L2"."ID": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "LPA_L2"."ID": invalid identifier

If I change

group technicien by technicien.Nom into g
select LinqDatabaseFunctions.ListAgg(g.Key)).FirstOrDefault(),

by

select LinqDatabaseFunctions.ListAgg(techncien.Nom)).FirstOrDefault(),

the query runs but returns only the first row. It is due to the FirstOrDefault() clause which generate the ROWNUM filter.

SELECT "LPLA_1"."ID"            AS "ID",
       (SELECT LISTAGG("LPA_L3"."NOM", ', ')
                 WITHIN GROUP(ORDER BY "LPA_L3"."NOM") AS "LPFA_5"
        FROM   ("NEPTUNE_AEP"."TECHNICIEN_OPERATION" "LPA_L2"
                INNER JOIN "NEPTUNE_AEP"."TECHNICIEN" "LPA_L3"
                        ON "LPA_L2"."ID_TECHNICIEN" = "LPA_L3"."ID")
        WHERE  (((( "LPA_L2"."ID_OPERATION" = "LPLA_1"."ID" ))))
               AND ROWNUM <= 1) AS "CODESTECHNICIENS"
FROM   "NEPTUNE_AEP"."OPERATION" "LPLA_1" 

If I remove the AND ROWNUM <= 1, the result is the expected one.

But if I don't use FirstOrDefault() clause but ToList(), the code doesn't compile. Indeed, my Techniciens property is a string and must be a string while the query would return a list of string.

=> How to not add ROWNUM <= 1 filter but return a string and not a list of string?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Apr-2021 12:21:49   

@quote The query throws an exception

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ORA-00904: "LPA_L2"."ID": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
 ---> Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "LPA_L2"."ID": invalid identifier

@quote That's weird, as there's no LPLA_2.ID in the query at all...

You get the rownum as you specify a firstordefault value directly on the query. This is seen as part of the query and thus is generated into the SQL. The query you specify does in theory result in a set of strings, however in your case it will always be a set of 1. The sql generator doesn't know that.

To trick it, you can do the following: any code that's not seen as part of the linq query parts to use to generate sql is compiled into a single projection lambda, so if you specify normal C# to index in a list, it'll be fine.

// your query
var query = from operation in LinqMetaData.Operation
            select new OperationViewModel
            {
                Id = operation.Id,
                Techniciens = PickFirst(from technicienOperation in LinqMetaData.TechnicienOperation
                                join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                where technicienOperation.IdOperation == operation.Id
                                group technicien by technicien.Nom into g
                                select LinqDatabaseFunctions.ListAgg(g.Key)).ToList()),
            };

// helper method            
private string PickFirst(List<string> toPickFrom)
{
    return toPickFrom==null || toPickFrom.Count <=0 ? string.Empty : toPickFrom[0];
}

I placed the method separately from the projection and not inlined the code there as it might see the ?: as an IIF and will execute that inside the query anyway. (I haven't checked, but you can find that out very quickly) The PickFirst makes sure the query compiles and as it's an in-memory method, it simply gets the results of the nested query. This does mean the query is using 2 queries and it's merged at runtime.

I'm still puzzled what the exact error is that you get with

SELECT "LPLA_1"."id"        AS "Id",  
       (SELECT Listagg("LPA_L2"."nom", ', ')  
                 within GROUP(ORDER BY "LPA_L2"."Nom") AS "LPFA_5"  
        FROM   (SELECT "LPA_L4"."nom" AS "Nom"  
                FROM   ("NEPTUNE_AEP"."technicien_operation" "LPA_L3"  
                        inner join "NEPTUNE_AEP"."technicien" "LPA_L4"  
                                ON "LPA_L3"."id_technicien" = "LPA_L4"."id")  
                WHERE  ((( "LPA_L3"."id_operation" = "LPLA_1"."id" )))  
                GROUP  BY "LPA_L4"."nom") "LPA_L2"  
        WHERE  ROWNUM <= 1) AS "Techniciens"  
FROM   "NEPTUNE_AEP"."operation" "LPLA_1" 

As this query should work fine? (I also find it odd the join is lower case as our engine generates joins in UPPER case)

Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 08-Apr-2021 18:15:37   

First version

CodesTechniciens = PickFirst((from technicienOperation in LinqMetaData.TechnicienOperation
                                                              join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                                              where technicienOperation.IdOperation == operation.Id
                                                              group technicien by technicien.Nom into g
                                                              select LinqDatabaseFunctions.ListAgg(g.Key)).ToList()),

The following error occurs

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: Couldn't create any correlation filter lambda because the nested query didn't have any correlation filters. Please specify a filter in the nested query to tie the nested query to the parent query
   at SD.LLBLGen.Pro.LinqSupportClasses.ValueListProjectionDefinition.PostProcessNestedQueries(ITemplateGroupSpecificCreator frameworkElementCreator, IElementCreatorCore generatedCodeElementCreator, MappingTracker trackedMappings)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleProjectionExpression(ProjectionExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleAggregateExpression(AggregateExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecute(Expression expression, Type resultType)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

2nd version (without the group by because in fact it's already done in the ListAgg function)

                                 CodesTechniciens = PickFirst((from technicienOperation in LinqMetaData.TechnicienOperation
                                                               join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                                               where technicienOperation.IdOperation == operation.Id
                                                               select LinqDatabaseFunctions.ListAgg(technicien.Nom)).ToList()),

The generated query is

Query: SELECT LISTAGG("LPA_L2"."NOM", ', ') WITHIN GROUP(ORDER BY "LPA_L2"."NOM") AS "LPFA_49", "LPA_L1"."ID_OPERATION" AS "IdOperation" FROM ("NEPTUNE_AEP"."TECHNICIEN_OPERATION" "LPA_L1" INNER JOIN "NEPTUNE_AEP"."TECHNICIEN" "LPA_L2" ON "LPA_L1"."ID_TECHNICIEN" = "LPA_L2"."ID") WHERE ( ( ( "LPA_L1"."ID_OPERATION" IN (:p1, :p2))))
    Parameter: :p1 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 509.

The following error occurs

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ORA-00937: not a single-group group function. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
 ---> Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00937: not a single-group group function
   at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.ExecuteAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchDataReaderAsync(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjectionAsync(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary`2 typeConvertersToRun, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjectionAsync(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategyAsync(Func`1 toExecute, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjectionAsync(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjectionAsync(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjectionAsync(QueryExpression toExecute, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjectionAsync(QueryExpression toExecute, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpressionAsync(Expression handledExpression, Type typeForPostProcessing, Boolean performPostProcessing, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.PerformExecuteAsync(Expression expression, Type typeForPostProcessing, Boolean performPostProcessing, CancellationToken cancellationToken)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 09-Apr-2021 00:28:33   

2nd version: to use a ListAgg you need to specify a GroupBy, as ListAgg is another Aggregate function that can't be used in the select list without Grouping.

1st version: I don't see the outer query where the correlation is needed for, where is the "from operations" part?

Posts: 61
Joined: 14-Feb-2017
# Posted on: 09-Apr-2021 08:08:16   

1) In SQL version, you can see that there is no GROUP BY clause because the the group by is already done by the LISTAGG WITHIN GROUP(X) method. 2) I added a project which contains unit tests which execute different queries (SQL version, with FIrstOrDefaut, with ToList, with ToList and PickFirst, ...) It will permit us to reproduce the matter I encounter. Note : I included a sql script to let you recreate database in your environment.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 09:44:07   

I'm sorry I advised a linq query that didn't run properly, I overlooked the fact that the nested query has to have a value in the projection to tie it to the outer query otherwise there's no merge possible, but as it's a group by query it can't add that to the projection as that would cause the grouping to change, hence it avoids doing that (and thus runs into this error).

The second version fails as in this case it adds the id to tie the nested query to the outer query to tie the nested query to the outer query as there's no group by, but oracle doesn't like it as it's not valid sql.

To be honest this will be a bit problematic, I'm afraid. Linq is too rigid in this case. I'll see if I can re-create your schema here and see if I can formulate the query in queryspec which at least allows you to run the query.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 10:24:57   

It turned out to be very simple simple_smile

[TestMethod]
public void TestListAggOracleFunction_V2()
{
    var query = from operation in LinqMetaData.Operation
                select new OperationViewModel
                {
                    Id = operation.Id,
                    Techniciens = (from technicienOperation in LinqMetaData.TechnicienOperation
                                    join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                    where technicienOperation.IdOperation == operation.Id
                                    select new { Names = LinqDatabaseFunctions.ListAgg(technicien.Nom)}
                                    ).Select(n=>n.Names).FirstOrDefault(),
                };

    var result = query.ToList();
    DumpResult(result, "v2");
}

gives:

ListAggTests.ListAggTests.TestListAggOracleFunction_V2

Results of version v2
------------------------------------
Result 0: ID: 363, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 1: ID: 364, Techniciens:  O t i s, S y l v a i n
Result 2: ID: 365, Techniciens:  O t i s, S y l v a i n
Result 3: ID: 366, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 4: ID: 367, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 5: ID: 368, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 6: ID: 369, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 7: ID: 370, Techniciens:  O t i s, S y l v a i n

Executed Sql Query:

SELECT  "LPLA_1"."ID" AS "Id", 
        (
            SELECT "LPA_L2"."Names" 
            FROM 
            (
                SELECT LISTAGG("LPA_L4"."NOM", ',') WITHIN GROUP(ORDER BY "LPA_L4"."NOM") AS "Names" 
                FROM ("SCOTT"."TECHNICIEN_OPERATION" "LPA_L3" INNER JOIN "SCOTT"."TECHNICIEN" "LPA_L4" ON "LPA_L3"."ID_TECHNICIEN" = "LPA_L4"."ID") 
                WHERE ( ( ( "LPA_L3"."ID_OPERATION" = "LPLA_1"."ID")))
            ) "LPA_L2" 
            WHERE rownum <=  1
        ) AS "Techniciens" 
FROM "SCOTT"."OPERATION" "LPLA_1"

(DumpResult is a simple method which does a console write of the two values)

The RowNum<=1 ruined the query, so the way to make that work is to first project the set you want (the one with the LISTAGG()) and then perform the FirstOrDefault to make it compile, hence the select into an anonymous type and an extra select.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 09-Apr-2021 10:38:41   

I probably need to update to latest version because in my case I have the same error

Generated Sql query: 
    Query: SELECT "LPA_L4"."NOM" AS "Names" FROM ("NEPTUNE_AEP"."TECHNICIEN_OPERATION" "LPA_L3" INNER JOIN "NEPTUNE_AEP"."TECHNICIEN" "LPA_L4" ON "LPA_L3"."ID_TECHNICIEN" = "LPA_L4"."ID") WHERE ( ( ( "LPA_L3"."ID_OPERATION" = "LPLA_1"."ID")))
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT "LPA_L2"."Names" FROM (SELECT "LPA_L4"."NOM" AS "Names" FROM ("NEPTUNE_AEP"."TECHNICIEN_OPERATION" "LPA_L3" INNER JOIN "NEPTUNE_AEP"."TECHNICIEN" "LPA_L4" ON "LPA_L3"."ID_TECHNICIEN" = "LPA_L4"."ID") WHERE ( ( ( "LPA_L3"."ID_OPERATION" = "LPLA_1"."ID")))) "LPA_L2" WHERE rownum <=  1
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT "LPLA_1"."ID" AS "Id", (SELECT "LPA_L2"."Names" FROM (SELECT "LPA_L4"."NOM" AS "Names" FROM ("NEPTUNE_AEP"."TECHNICIEN_OPERATION" "LPA_L3" INNER JOIN "NEPTUNE_AEP"."TECHNICIEN" "LPA_L4" ON "LPA_L3"."ID_TECHNICIEN" = "LPA_L4"."ID") WHERE ( ( ( "LPA_L3"."ID_OPERATION" = "LPLA_1"."ID")))) "LPA_L2" WHERE rownum <=  1) AS "Techniciens" FROM "NEPTUNE_AEP"."OPERATION" "LPLA_1"
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
'testhost.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.4\System.Diagnostics.StackTrace.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'testhost.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.4\System.Net.Security.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'testhost.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.4\System.Security.Cryptography.Csp.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'testhost.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.4\System.Security.Cryptography.Primitives.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'testhost.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.4\System.Diagnostics.FileVersionInfo.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Exception thrown: 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' in SD.LLBLGen.Pro.ORMSupportClasses.dll
An exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.dll but was not handled in user code
An exception was caught during the execution of a retrieval query: ORA-00904: "LPLA_1"."ID": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
Posts: 61
Joined: 14-Feb-2017
# Posted on: 09-Apr-2021 11:36:58   

Sorry but it doesn't work on my side even after update to the 5.8.1-hotfix-20210408. Same error.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 12:56:27   

Your test forgot to pass the functionmappings in the LinqMetaData property where you construct that object. The query above shows the custom function isn't executed, hence it doesn't work. simple_smile So you have to do:

public LinqMetaData LinqMetaData
{
    get
    {
        return new LinqMetaData(this.DataAccessAdapter, new LinqDatabaseFunctionMappings());
    }
}

I ran your tests with 5.6.6 btw, latest 5.6 version from nuget, as the generated code was made with 5.6

Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 09-Apr-2021 14:20:22   

Indeed, I forgot it. But I don't really understand because I made the change (as you can see below) and I alays have the error.

using Microsoft.VisualStudio.TestTools.UnitTesting;
using ListAggTests.Entities.Linq;
using System.Linq;
using System.Collections.Generic;
using ListAggTests.Entities.DatabaseSpecific;

namespace ListAggTests
{
    [TestClass]
    public class ListAggTests
    {
        public DataAccessAdapter DataAccessAdapter
        {
            get
            {
                string connectionString = "Pooling=false;Data Source=docker01.dlmsoft.local:15891;User Id=NEPTUNE_AEP;Password=???;Connection Timeout=0";

                LLBLGenConfigurationHelper.Configure("NEPTUNE_AEP", connectionString, true);

                var dataAccessAdapter = new DataAccessAdapter();
                dataAccessAdapter.ConnectionString = connectionString;
                return dataAccessAdapter;
            }
        }

        public LinqMetaData LinqMetaData
        {
            get
            {
                return new LinqMetaData(this.DataAccessAdapter, new LinqDatabaseFunctionMappings());
            }
        }

        /// <summary>
        /// Use 
        /// * LISTAGG function (WITHIN GROUP)
        /// 
        /// RESULT : OK but returns "\0O\0t\0i\0s, \0S\0y\0l\0v\0a\0i\0n, \0W\0a\0l\0a\0a" instead of "Otis, Sylvain, Walaa"
        /// Probably a encoding format matter !!!
        /// </summary>
        [TestMethod]
        public void TestListAggOracleFunction_V1()
        {
            string query =
                @"SELECT
                      OPERATION.ID,
                      (SELECT LISTAGG(TECHNICIEN.NOM, ',') WITHIN GROUP(ORDER BY TECHNICIEN.NOM)
                        FROM TECHNICIEN_OPERATION
                        INNER JOIN TECHNICIEN ON TECHNICIEN.ID = TECHNICIEN_OPERATION.ID_TECHNICIEN
                        WHERE TECHNICIEN_OPERATION.ID_OPERATION = OPERATION.ID
                      ) AS Techniciens
                    FROM OPERATION";

            var result = DataAccessAdapter.FetchQuery<OperationViewModel>(query);
        }        

        [TestMethod]
        public void TestListAggOracleFunction_V2()
        {
            var query = from operation in LinqMetaData.Operation
                        select new OperationViewModel
                        {
                            Id = operation.Id,
                            Techniciens = (from technicienOperation in LinqMetaData.TechnicienOperation
                                           join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                           where technicienOperation.IdOperation == operation.Id
                                           select LinqDatabaseFunctions.ListAgg(technicien.Nom)).FirstOrDefault(),
                        };

            var result = query.ToList();
        }

        [TestMethod]
        public void TestListAggOracleFunction_V2_Otis()
        {
            var query = from operation in LinqMetaData.Operation
                        select new OperationViewModel
                        {
                            Id = operation.Id,
                            Techniciens = (from technicienOperation in LinqMetaData.TechnicienOperation
                                           join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                           where technicienOperation.IdOperation == operation.Id
                                           select new { Names = LinqDatabaseFunctions.ListAgg(technicien.Nom) }
                                            ).Select(n => n.Names).FirstOrDefault(),
                        };

            var result = query.ToList();
        }

        public class OperationViewModel
        {
            public long Id { get; set; }
            public string Techniciens { get; set; }
        }
    }
}
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 14:38:12   

That's the exact code you used which failed? Will see if I can reproduce it. Be sure that if you use a newer runtime, you have to regenerate the code, as it might be things are a bit different. It's not a given, but we have had issues reported because of that.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 15:25:29   

v2 otis:

ListAggTests.ListAggTests.TestListAggOracleFunction_V2_Otis

Results of version v2_Otis
------------------------------------
Result 0: ID: 363, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 1: ID: 364, Techniciens:  O t i s, S y l v a i n
Result 2: ID: 365, Techniciens:  O t i s, S y l v a i n
Result 3: ID: 366, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 4: ID: 367, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 5: ID: 368, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 6: ID: 369, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 7: ID: 370, Techniciens:  O t i s, S y l v a i n

query:

SELECT  "LPLA_1"."ID" AS "Id", 
        (
            SELECT "LPA_L2"."Names" 
            FROM 
            (
                SELECT LISTAGG("LPA_L4"."NOM", ',') WITHIN GROUP(ORDER BY "LPA_L4"."NOM") AS "Names" 
                FROM ("SCOTT"."TECHNICIEN_OPERATION" "LPA_L3" INNER JOIN "SCOTT"."TECHNICIEN" "LPA_L4" ON "LPA_L3"."ID_TECHNICIEN" = "LPA_L4"."ID") 
                WHERE ( ( ( "LPA_L3"."ID_OPERATION" = "LPLA_1"."ID")))
            ) "LPA_L2" WHERE rownum <=  1
        ) AS "Techniciens" 
FROM "SCOTT"."OPERATION" "LPLA_1"

Will now see what's wrong with your tests compared to mine

(edit) running them with v5.8.0 runtime (latest non-hotfix, the hotfix builds contain edge case changes not affected by this) works fine too..
(edit) well, the name overwriting code in your test is relying on case sensitive search. If I fix that, it produces the right query even with your persistenceinfo and name overwriting and my connection string.

I've attached my code, perhaps it's something you see what's different, I don't see it. We test on 12c btw, but that shouldn't be important.

Attachments
Filename File size Added on Approval
ListAggTest.zip 33,139 09-Apr-2021 15:35.27 Approved
Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 09-Apr-2021 15:48:51   

Which version of Oracle do you have ? Mine is Oracle 11G.

Because I can't figure why it works on your side and not on mine.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 16:14:43   

gilles.marceau wrote:

Which version of Oracle do you have ? Mine is Oracle 11G.

Because I can't figure why it works on your side and not on mine.

12C, but I don't think it's depending on the oracle version, as the query looks OK to run on older versions too. The post you made higher up where you tested my fix has a SQL query (which failed) which lacks the ListAgg call, so not sure how genuine that test was.

The code you posted above, with v2_otis, that test, what SQL query does that produce? Could you enable the ORMQueryExecution trace to see what exact query is executed?

If you copy/paste the SQL query that's been produced by my test (and replace "SCOTT" with your own schema name) does it give an error when you execute it directly on the database e.g. with toad or other database tool?

Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 09-Apr-2021 17:00:24   

1) I use your project
2) I launch the TestListAggOracleFunction_V2_Otis test method

        [TestMethod]
        public void TestListAggOracleFunction_V2_Otis()
        {
            var query = from operation in LinqMetaData.Operation
                        select new OperationViewModel
                               {
                                   Id = operation.Id,
                                   Techniciens = (from technicienOperation in LinqMetaData.TechnicienOperation
                                                  join technicien in LinqMetaData.Technicien on technicienOperation.IdTechnicien equals technicien.Id
                                                  where technicienOperation.IdOperation == operation.Id
                                                  select new { Names = LinqDatabaseFunctions.ListAgg(technicien.Nom) }
                                       ).Select(n => n.Names).FirstOrDefault(),
                               };

            var result = query.ToList();
            DumpResult(result, "v2_Otis");
        }

3) I use the ORM Profiler. The generated query is

SELECT "LPLA_1"."ID"        AS "Id",
       (SELECT "LPA_L2"."Names"
        FROM   (SELECT LISTAGG("LPA_L4"."NOM",
                               ',') WITHIN GROUP(ORDER BY "LPA_L4"."NOM") AS "Names"
                FROM   ("NEPTUNE_AEP"."TECHNICIEN_OPERATION" "LPA_L3"
                        INNER JOIN "NEPTUNE_AEP"."TECHNICIEN" "LPA_L4"
                            ON "LPA_L3"."ID_TECHNICIEN" = "LPA_L4"."ID")
                WHERE  ((("LPA_L3"."ID_OPERATION" = "LPLA_1"."ID")))) "LPA_L2"
        WHERE  rownum <= 1) AS "Techniciens"
FROM   "NEPTUNE_AEP"."OPERATION" "LPLA_1" 

and give the following error ORA-00904: "LPLA_1"."ID": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

4) If I execute your query (renaming SCOTT to NEPTUNE_AEP) on my database, I have the same error.

Really strange.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 18:52:04   

Ran the query (after replacing your schema name with scott which is the test db we use) directly on the DB using DBeaver, no errors.

But something occurred to me... in older Oracle versions, the number of nested levels in queries was limited. We had the same problem with paging queries and derived tables some years back on e.g. 10g. I'll ask an oracle expert I know to see if he knows more about this.

Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 09-Apr-2021 19:05:06   

OK thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Apr-2021 19:24:50   

Yes, it's due to the number of nesting. You can't correlate a subquery over 2 levels in 11G, this was fixed afterwards (12C works fine) See: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=cefbedcf3188daf7d18d646990d04dc9 If you switch to 18C you see it works.

It requires a rewrite that will make linq compile and not end up with the # of nesting... rage

I'll see if I can rewrite it in queryspec as it's less strict or perhaps the low-level API for this particular case. Will do that on Monday

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 12-Apr-2021 11:47:58   

Using QuerySpec allows to define a scalar query without the rownum exactly like required.

var qf = new QueryFactory();
var q = qf.Operation.TargetAs("O")
          .Select(() => new OperationViewModel()
                        {
                            Id = OperationFields.Id.ToValue<Int64>(),
                            Techniciens = qf.TechnicienOperation
                                            .From(QueryTarget.InnerJoin(qf.Technicien.As("T"))
                                                                .On(TechnicienOperationFields.IdTechnicien.Equal(TechnicienFields.Id.Source("T"))))
                                            .Where(TechnicienOperationFields.IdOperation.Equal(OperationFields.Id.Source("O")))
                                            .Select(QsDatabaseFunctions.ListAgg(TechnicienFields.Nom.Source("T")))
                                            .ToScalar().ToValue<string>()
                        });
q.CustomFunctionMappingStore = new QsDatabaseFunctionMappings();
var result = DataAccessAdapter.FetchQuery(q);
DumpResult(result, "v2_Otis_qs");

function definitions:

public static class QsDatabaseFunctions
{
    public static FunctionMappingExpression ListAgg(IEntityFieldCore field)
    {
        return new FunctionMappingExpression(typeof(QsDatabaseFunctions), "ListAgg", 1, field);
    }
}

public class QsDatabaseFunctionMappings : FunctionMappingStore
{
    public QsDatabaseFunctionMappings()
        : base()
    {
        this.Add(new FunctionMapping(typeof(QsDatabaseFunctions), "ListAgg", 1, "LISTAGG({0}, ',') WITHIN GROUP(ORDER BY {0})"));
    }
}

Query:

SELECT  "LPA_O1"."ID" AS "Id", 
        (
            SELECT LISTAGG("LPA_T2"."NOM", ',') WITHIN GROUP(ORDER BY "LPA_T2"."NOM") AS "LLBLV_1" 
            FROM ("SCOTT"."TECHNICIEN_OPERATION" INNER JOIN "SCOTT"."TECHNICIEN" "LPA_T2" 
                ON "SCOTT"."TECHNICIEN_OPERATION"."ID_TECHNICIEN" = "LPA_T2"."ID") 
            WHERE ( ( "SCOTT"."TECHNICIEN_OPERATION"."ID_OPERATION" = "LPA_O1"."ID"))
        ) AS "Techniciens" 
FROM "SCOTT"."OPERATION" "LPA_O1"

Result:

Results of version v2_Otis_qs
------------------------------------
Result 0: ID: 363, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 1: ID: 364, Techniciens:  O t i s, S y l v a i n
Result 2: ID: 365, Techniciens:  O t i s, S y l v a i n
Result 3: ID: 366, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 4: ID: 367, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 5: ID: 368, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 6: ID: 369, Techniciens:  O t i s, S y l v a i n, W a l a a
Result 7: ID: 370, Techniciens:  O t i s, S y l v a i n

It's a bit more verbose than the linq variant, but I couldn't find a way to trick linq into emitting the query you wanted. (It also might be some aliases aren't necessary, haven't checked, this at least gives a query which should work on oracle 11g)

Frans Bouma | Lead developer LLBLGen Pro
Posts: 61
Joined: 14-Feb-2017
# Posted on: 13-Apr-2021 14:23:45   

I had to do something else but as soon as possible, I will try the query you send me.

UPDATE: I test on my side and with the QuerySpec version, I have been able to do what I want. Just to inform you that I change this

public class QsDatabaseFunctionMappings : FunctionMappingStore
{
    public QsDatabaseFunctionMappings()
        : base()
    {
        this.Add(new FunctionMapping(typeof(QsDatabaseFunctions), "ListAgg", 1, "LISTAGG({0}, ',') WITHIN GROUP(ORDER BY {0})"));
    }
}

by

public class QsDatabaseFunctionMappings : FunctionMappingStore
{
    public QsDatabaseFunctionMappings()
        : base()
    {
        this.Add(new FunctionMapping(typeof(QsDatabaseFunctions), "ListAgg", 1, "REPLACE(LISTAGG({0}, ',') WITHIN GROUP(ORDER BY {0}), chr(0))"));
    }
}

else the result was "\0O\0t\0i\0s,\0S\0y\0l\0v\0a\0i\0n, \0W\0a\0l\0a\0a" instead of "Otis, Sylvain, Walaa".

Thanks

1  /  2