Wrong Sql Produced Expecting Case When Or NVL

[gokhanabatay]

Below linq does not produce NVL for '(crdCard.FromCrdCard.CardNo ?? "")' also it sees "" as constant and does not cache linq expression cache. #2222 CardNo property is a string, MBR_ID is Dynamic Filter Condition.

Tested with NHibernate Master Code

var cards = (from crdCard in Query<CrdCard>().Where(predicate)
             select new CustomerCardsDto()
             {
                 CustomerNo = crdCard.CustomerNo,
                 CardNo = crdCard.CardNo,
                 ProductName = crdCard.CrdProduct.Description.GetText(CurrentLanguage),
                 StatCode = crdCard.StatCode.Substring(0, 1),
                 SubStatCode = crdCard.StatCode.Substring(1, 1),
                 StatDescription = crdCard.CrdStatDef.Description.GetText(CurrentLanguage),
                 PrevCardNo = (crdCard.FromCrdCard.CardNo ?? "")
             })
             .ToList()

SELECT crdcard0_.CUSTOMER_NO                      AS col_1_0_,
       crdcard0_.CARD_NO                          AS col_2_0_,
       CFG.FN_GET_LANGUAGE_TEXT (crdproduct3_.DESCRIPTION, :p0)
          AS col_8_0_,
       SUBSTR (crdcard0_.STAT_CODE, :p1 + 1, :p2) AS col_11_0_,
       SUBSTR (crdcard0_.STAT_CODE, :p3 + 1, :p4) AS col_12_0_,
       CFG.FN_GET_LANGUAGE_TEXT (crdstatdef6_.DESCRIPTION, :p5)
          AS col_13_0_,
       crdcard10_.CARD_NO                         AS col_22_0_
  FROM ISS.CRD_CARD crdcard0_
       LEFT OUTER JOIN ISS.CRD_PRODUCT crdproduct3_
          ON     crdcard0_.PRODUCT_GUID = crdproduct3_.GUID
             AND crdproduct3_.MBR_ID = :p6
       LEFT OUTER JOIN ISS.CRD_STAT_DEF crdstatdef6_
          ON crdcard0_.STAT_CODE = crdstatdef6_.CODE
       LEFT OUTER JOIN ISS.CRD_CARD crdcard10_
          ON     crdcard0_.FROM_CARD_GUID = crdcard10_.GUID
             AND crdcard10_.MBR_ID = :p6
             AND (crdcard10_.STATUS = 1)
 WHERE     crdcard0_.MBR_ID = :p6
       AND (crdcard0_.STATUS = 1)
       AND crdcard0_.CUSTOMER_NO = :p14

Also linq statements does not produce "Case when condition then value1 else value2 end" PrevCardNo = crdCard.FromCrdCard.Guid > 0 ? crdCard.FromCrdCard.CardNo : "" produces same sql above. Also "0" and "" seems a constant and query expression is not cached, it cause performance issue.

var cards = (from crdCard in Query<CrdCard>().Where(predicate)
             select new CustomerCardsDto()
             {
                 CustomerNo = crdCard.CustomerNo,
                 CardNo = crdCard.CardNo,
                 ProductName = crdCard.CrdProduct.Description.GetText(CurrentLanguage),
                 StatCode = crdCard.StatCode.Substring(0, 1),
                 SubStatCode = crdCard.StatCode.Substring(1, 1),
                 StatDescription = crdCard.CrdStatDef.Description.GetText(CurrentLanguage),
                 PrevCardNo = crdCard.FromCrdCard.Guid > 0 ? crdCard.FromCrdCard.CardNo :  ""
             })
             .ToList()

[maca88]

Below linq does not procedure NVL for '(crdCard.FromCrdCard.CardNo ?? "")' Also linq statements does not produce "Case when condition then value1 else value2 end"

Those two issues should be fixed in #2079.