Hi,

I would like to retrieve for each "Information" the id of the information and the date of the last "measure" that has been imported. I write the following Linq query

            var query= 
                        from informationSupervision in LinqMetaData.InformationSupervision
                        select new TestModel
                        {
                            IdInformationSupervision = informationSupervision.Id,
                            DateDerniereMesureImportee = (from mesure in LinqMetaData.Mesure
                                                          join tacheImportSupervision in LinqMetaData.TacheImportSupervision on mesure.IdTacheImportSupervision equals tacheImportSupervision.Id
                                                          where tacheImportSupervision.IdInformationSupervision == informationSupervision.Id
                                                          select mesure.DateMesure).Max()
                        };
            var data = await query.ToListAsync();

but encounter the following error : Oracle.ManagedDataAccess.Client.OracleException: 'ORA-00904: "LPLA_1"."ID": invalid identifier The generated SQL is the following

SELECT "LPLA_1"."ID" AS "IdInformationSupervision", 
  (
    SELECT * FROM 
    (
      SELECT "LPA_L2"."DATE_MESURE" AS "DateMesure" 
      FROM "NEPTUNE_AEP"."MESURE" "LPA_L2" 
      INNER JOIN "NEPTUNE_AEP"."TACHE_IMPORT_SUPERVISION" "LPA_L3" ON "LPA_L2"."ID_TACHE_IMPORT_SUPERVISION" = "LPA_L3"."ID"
      WHERE "LPA_L3"."ID_INFORMATION_SUPERVISION" = "LPLA_1"."ID" 
      ORDER BY "LPA_L2"."DATE_MESURE" ASC
    )
  WHERE rownum <= 1
  ) AS "DateDerniereMesureImportee" 
  FROM "NEPTUNE_AEP"."INFORMATION_SUPERVISION" "LPLA_1"

Note
- If I remove the sentence "where tacheImportSupervision.IdInformationSupervision == informationSupervision.Id", no error occurs.
- If I remove the Max() keyword, no error occurs

Could you help me

Technical info * LLBLGEN 5.9 version * .NET 5

The 'Max()' query should appear as a Max(field) call in the projection, not as an orderby asc (which is the Min() ). You're sure that's the SQL from this particular query?

Also, the problem is an Oracle limitation, as the query uses a Rownum expression on a wrapped query which makes it impossible for oracle to refer to the outer scope due to too much nesting. This should be changed if you switch the compatibility mode to 12c in the runtime (either in the configuration file or through code). Could you test that please? (it should in that mode append a fetch next clause instead of the rownum )

Sorry I made a mistake when I copy the Linq query. Below you will find the Linq and SQL queries.

            var query1 = 
                        from informationSupervision in LinqMetaData.InformationSupervision
                        select new TestModel
                        {
                            IdInformationSupervision = informationSupervision.Id,
                            DateDerniereMesureImportee = (from mesure in LinqMetaData.Mesure
                                                          join tacheImportSupervision in LinqMetaData.TacheImportSupervision on mesure.IdTacheImportSupervision equals tacheImportSupervision.Id
                                                          where tacheImportSupervision.IdInformationSupervision == informationSupervision.Id
                                                          orderby mesure.DateMesure descending
                                                          select mesure.DateMesure).FirstOrDefault()
                        };
            var data = await query1.ToListAsync();

SELECT "LPLA_1"."id"        AS "IdInformationSupervision",
       (SELECT *
        FROM   (SELECT "LPA_L2"."date_mesure" AS "DateMesure"
                FROM   ("NEPTUNE_AEP"."mesure" "LPA_L2"
                        INNER JOIN "NEPTUNE_AEP"."tache_import_supervision"
                                   "LPA_L3"
                                ON "LPA_L2"."id_tache_import_supervision" =
                                   "LPA_L3"."id")
                WHERE  ((((( "LPA_L3"."id_information_supervision" =
                       "LPLA_1"."id" )))))
                ORDER  BY "LPA_L2"."date_mesure" DESC)
        WHERE  rownum <= 1) AS "DateDerniereMesureImportee"
FROM   "NEPTUNE_AEP"."information_supervision" "LPLA_1" 

If I use the 0racle12c compatibility, the same query is generated as

SELECT "LPLA_1"."ID" AS "IdInformationSupervision",
       (
                  SELECT     "LPA_L2"."DATE_MESURE" AS "DateMesure"
                  FROM       ("NEPTUNE_AEP"."MESURE" "LPA_L2"
                  INNER JOIN "NEPTUNE_AEP"."TACHE_IMPORT_SUPERVISION" "LPA_L3"
                  ON         "LPA_L2"."ID_TACHE_IMPORT_SUPERVISION" = "LPA_L3"."ID")
                  WHERE      ( ( ( ( (
                  "LPA_L3"."ID_INFORMATION_SUPERVISION" = "LPLA_1"."ID")))))
                  ORDER BY   "LPA_L2"."DATE_MESURE" DESCFETCH next :p2 rows only) AS "DateDerniereMesureImportee"
              
FROM   "NEPTUNE_AEP"."INFORMATION_SUPERVISION" "LPLA_1"
PARAMETER: :p2 : int32.LENGTH: 0.PRECISION: 0.SCALE: 0.DIRECTION: input.VALUE: 1.

which is not understood by the database which is Oracle 11G

Is there a "better" way to update the date of the INFORMATION_SUPERVISION table with the date of the last measure and this for each ID of INFORMATION_SUPERVISION ? Something like this in SQL

  UPDATE INFORMATION_SUPERVISION
    SET DATE_DERNIERE_MESURE_IMPORTEE = 
    (
          SELECT MAX(DATE_MESURE)
          FROM MESURE
          INNER JOIN TACHE_IMPORT_SUPERVISION ON TACHE_IMPORT_SUPERVISION.ID = MESURE.ID_TACHE_IMPORT_SUPERVISION
          WHERE TACHE_IMPORT_SUPERVISION.ID_INFORMATION_SUPERVISION = INFORMATION_SUPERVISION.ID
          GROUP BY INFORMATION_SUPERVISION.ID
    )

I don't think this last Group By is valid in the inner query. Is it?

A group by could be the solution tho, fetch the query with a groupby (with a max on the mesure.DateMesure column), so you get a 2 value set, and then use that to insert the data.

The limitation of the old oracle versions isn't solvable otherwise

OK thanks, I will to do this