[Firebird-net-provider] [FB-Tracker] Created: (DNET-870) Subselects in WHEN not generated correctly

[Jiri C. (JIRA) <tr...@fi...>]

Subselects in WHEN not generated correctly
------------------------------------------

                 Key: DNET-870
                 URL: http://tracker.firebirdsql.org/browse/DNET-870
             Project: .NET Data provider
          Issue Type: Bug
          Components: Entity Framework Core
    Affects Versions: 6.5.0.0
            Reporter: Jiri Cincura
            Assignee: Jiri Cincura
            Priority: Minor


return Ok(
             new {
              OP =  await db.HT_REZERVARI
                  .Where(r => r.ID == id)
                  .Select(r => new 
                     {
                         r.ID,
                         r.ID_CAMERA,
                         r.DATAI,
                         r.NRZILE,
                         r.STARE,
                         r.ID_CF,
                         r.IDOPERATOR,
                         r.FACTURAT,
                         r.FACTURAT3,
                         r.TIPFCCUR,
                         r.NR_FACT,
                         r.DATAF,
                         r.NR_FACTP,
                         r.DATAFP,
                         r.NR_FACT3,
                         DATAF3 =  r.HT_INFEXPED.Where(x => x.TIP == 3).Select(x => x.DATAF).FirstOrDefault(),
                         TAXESUPL= r.HT_TAXE.Any(t=>t.TIP==3) ? 1 : 0,
                         DENCL = r.CF.DENUMIRE,
                         DENCAMERA = r.HT_CAMERE.DEN,
                         DENZONA = r.HT_CAMERE.HT_ZONE.DENUMIRE,
                         r.IDGRUP,
                         DENGRUP = r.HT_REZGRUPE.DENUMIRE,
                         r.AVANS,
                         r.AVANSCUFC,
                         NR_FACTA = r.HT_INFEXPED.Where(x => x.TIP == 2).Select(x => x.NRFACT).FirstOrDefault(),
                         DATAFA = r.HT_INFEXPED.Where(x => x.TIP == 2).Select(x => x.DATAF).FirstOrDefault(),
                         r.NR_CAMERE,
                         RONLY = r.IDGRUP>0 && db.HT_REZERVARI.Any(re => re.IDGRUP == r.IDGRUP && re.ID !=r.ID 
                              && ((re.STARE==1 && re.NR_FACTP>0) || re.STARE==0 && re.NR_FACT>0)),
                         r.DATAPLATA
                     
                  }).ToListAsync(),

              PLATI = await db.HT_PLATI
              .Where(p => p.IDCAZARE == id)
              .Join(db.HT_REZERVARI, p => new {ID= p.IDCAZARE, p.TIPFC }, r => new { r.ID, TIPFC=r.TIPFCCUR }, (p, r) => new { p, r })
              .Select(g=> new { g.p.TIP, g.p.VALOARE })
              .ToListAsync()
              }  
             );
===
SELECT "r"."ID", "r"."ID_CAMERA", "r"."DATAI", "r"."NRZILE", "r"."STARE", "r"."ID_CF", "r"."IDOPERATOR", "r"."FACTURAT", "r"."FACTURAT3", "r"."TIPFCCUR", "r"."NR_FACT", "r"."DATAF", "r"."NR_FACTP", "r"."DATAFP", "r"."NR_FACT3", (
    SELECT FIRST 1 "x"."DATAF"
    FROM "HT_INFEXPED" AS "x"
    WHERE ("x"."TIP" = 3) AND ("r"."ID" = "x"."ID_REZERVARE")
) AS "DATAF3", CASE
    WHEN (
        SELECT CASE
            WHEN EXISTS (
                SELECT 1
                FROM "HT_TAXE" AS "t"
                WHERE ("t"."TIP" = 3) AND ("r"."ID" = "t"."ID_REZERVARE"))
            THEN TRUE ELSE FALSE
        END
    )
    FROM RDB$DATABASE = TRUE
    THEN 1 ELSE 0
END AS "TAXESUPL", "r.CF"."DENUMIRE" AS "DENCL", "r.HT_CAMERE"."DEN" AS "DENCAMERA", "r.HT_CAMERE.HT_ZONE"."DENUMIRE" AS "DENZONA", "r"."IDGRUP", "r.HT_REZGRUPE"."DENUMIRE" AS "DENGRUP", "r"."AVANS", "r"."AVANSCUFC", COALESCE((
    SELECT FIRST 1 "x0"."NRFACT"
    FROM "HT_INFEXPED" AS "x0"
    WHERE ("x0"."TIP" = 2) AND ("r"."ID" = "x0"."ID_REZERVARE")
), 0) AS "NR_FACTA", (
    SELECT FIRST 1 "x1"."DATAF"
    FROM "HT_INFEXPED" AS "x1"
    WHERE ("x1"."TIP" = 2) AND ("r"."ID" = "x1"."ID_REZERVARE")
) AS "DATAFA", "r"."NR_CAMERE", CASE
    WHEN ("r"."IDGRUP" > 0) AND ((
        SELECT CASE
            WHEN EXISTS (
                SELECT 1
                FROM "HT_REZERVARI" AS "re"
                WHERE (("re"."IDGRUP" = "r"."IDGRUP") AND ("re"."ID" <> "r"."ID")) AND ((("re"."STARE" = 1) AND ("re"."NR_FACTP" > 0)) OR (("re"."STARE" = 0) AND ("re"."NR_FACT" > 0))))
            THEN TRUE ELSE FALSE
        END
    )
    FROM RDB$DATABASE = TRUE)
    THEN TRUE ELSE FALSE
END AS "RONLY", "r"."DATAPLATA"
FROM "HT_REZERVARI" AS "r"
INNER JOIN "HT_REZGRUPE" AS "r.HT_REZGRUPE" ON "r"."IDGRUP" = "r.HT_REZGRUPE"."ID"
INNER JOIN "HT_CAMERE" AS "r.HT_CAMERE" ON "r"."ID_CAMERA" = "r.HT_CAMERE"."ID"
INNER JOIN "HT_ZONE" AS "r.HT_CAMERE.HT_ZONE" ON "r.HT_CAMERE"."ID_ZONA" = "r.HT_CAMERE.HT_ZONE"."ID"
INNER JOIN "CF" AS "r.CF" ON "r"."ID_CF" = "r.CF"."ID"
WHERE "r"."ID" = @__id_0

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira