Issue Details (XML | Word | Printable)

Key: DNET-870
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Minor Minor
Assignee: Jiri Cincura
Reporter: Jiri Cincura
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Subselects in CASE/WHEN not generated correctly

Created: 18/Feb/19 03:35 PM   Updated: 18/Feb/19 04:04 PM
Component/s: Entity Framework Core
Affects Version/s: 6.5.0.0
Fix Version/s: 6.6.0.0

File Attachments: 1. Zip Archive models.zip (5 kB)



 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jiri Cincura made changes - 18/Feb/19 03:36 PM
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 6.6.0.0 [ 10890 ]
Resolution Fixed [ 1 ]
Jiri Cincura made changes - 18/Feb/19 03:36 PM
Summary Subselects in WHEN not generated correctly Subselects in CASE/WHEN not generated correctly
Jiri Cincura made changes - 18/Feb/19 04:04 PM
Attachment models.zip [ 13322 ]