Skip to content

Wrong Sql Produced Expecting Case When Or NVL #2234

Open
@gokhanabatay

Description

@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()

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions