|
Problem IC77827
|
Status: Closed |
LINQ EF QUERIES THAT USE CONTAINS, STARTSWITH OR ENDSWITH FUNCTIONS GENERATE INVALID SQL FOR IDS ON .NET 4.0 |
| product: |
DB2 CONNECT / DB2CONNCT / 970 - DB2 |
| 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." |
| 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. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 28.07.2011
07.12.2011
07.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
