IBM Support

IC77827: LINQ EF QUERIES THAT USE CONTAINS, STARTSWITH OR ENDSWITH FUNCTIONS GENERATE INVALID SQL FOR IDS ON .NET 4.0

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as fixed if next.

Error description

  • When Contains, StartsWith or EndsWith is used in the Where
    clause of LINQ query, the .NET 4.0 runtime converts it to LIKE
    expression.  Moreover, when a string variable, instead of a
    contant, is used as input to one of those functions, the
    generated LIKE expression contains an ESCAPE clause with the
    default escape character of '\'.  Since '\' is a constant
    expression, the EF providers' SQL Generator converts it to a
    CAST expression of CAST('\' as nvarchar(1)).  However, the IDS
    servers' ESCAPE clause doesn't support cast expressions, thus, a
    syntax error is generated for this SQL.
    From the customer perspective this means that while the
    folllwing LINQ query works fine:
    
    var plans = model.Plans.Where(p =>
    p.plan.StartsWith("AND")).Select(p =>
    new{p.plan,p.id}).ToList();
    
    The same query that uses a variable instead of constant fails:
    
    var startsWith = "AND";
    var plans = model.Plans.Where(p =>
    p.plan.StartsWith(startsWith)).Select(p =>
    new{p.plan,p.id}).ToList();
    
    with the following error message:  "ERROR 42000 IBMIDS/UNIX64 A
    syntax error has occurred."
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * .NET 4.0 applications using EF 4.0 against IDS server        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When Contains, StartsWith or EndsWith is used in the Where   *
    *                                                              *
    * clause of LINQ query, the .NET 4.0 runtime converts it to    *
    * LIKE                                                         *
    * expression.  Moreover, when a string variable, instead of a  *
    *                                                              *
    * contant, is used as input to one of those functions, the     *
    *                                                              *
    * generated LIKE expression contains an ESCAPE clause with the *
    *                                                              *
    * default escape character of '\'.  Since '\' is a constant    *
    *                                                              *
    * expression, the EF providers' SQL Generator converts it to a *
    *                                                              *
    * CAST expression of CAST('\' as nvarchar(1)).  However, the   *
    * IDS                                                          *
    * servers' ESCAPE clause doesn't support cast expressions,     *
    * thus, a                                                      *
    * syntax error is generated for this SQL.                      *
    *                                                              *
    * From the customer perspective this means that while the      *
    *                                                              *
    * folllwing LINQ query works fine:                             *
    *                                                              *
    *                                                              *
    *                                                              *
    * var plans = model.Plans.Where(p =>                           *
    *                                                              *
    * p.plan.StartsWith("AND")).Select(p =>                        *
    *                                                              *
    * new{p.plan,p.id}).ToList();                                  *
    *                                                              *
    *                                                              *
    *                                                              *
    * The same query that uses a variable instead of constant      *
    * fails:                                                       *
    *                                                              *
    *                                                              *
    * var startsWith = "AND";                                      *
    *                                                              *
    * var plans = model.Plans.Where(p =>                           *
    *                                                              *
    * p.plan.StartsWith(startsWith)).Select(p =>                   *
    *                                                              *
    * new{p.plan,p.id}).ToList();                                  *
    *                                                              *
    *                                                              *
    *                                                              *
    * with the following error message:  "ERROR 42000              *
    * IBMIDS/UNIX64 A                                              *
    * syntax error has occurred."                                  *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade the client to V9.7 FP5.                              *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IC77827

  • Reported component name

    DB2 CONNECT

  • Reported component ID

    DB2CONNCT

  • Reported release

    970

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-07-28

  • Closed date

    2011-12-07

  • Last modified date

    2011-12-07

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC77827

Modified date: 07 December 2011