Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2012-03-30T18:06:54Z by SystemAdmin
kdawg
kdawg
9 Posts
ACCEPTED ANSWER

Pinned topic v9.7fp4 syntax error with .StartsWith() in LINQ to Entities

‏2011-05-13T23:35:55Z |
I was stoked when support for EFv4 was released in v9.7fp4! The first thing I tried after installing the new drivers was a LINQ to Entities query that utilized .StartsWith() and see if it was properly translated to SQL. A L2E query such as:

****
var plans = model.Plans.Where(p => p.plan.StartsWith("AND"))
.Select(p => new
{
p.plan,
p.id
}).ToList();
****

Worked great. It generated the following SQL:

****
SELECT Extent1.id AS id,
Extent1.plan AS plan
FROM "informix".Plans AS Extent1
WHERE Extent1.plan LIKE CAST('AND%' AS nvarchar(4))
****

But then, I tried .StartsWith() with a string variable instead of a string literal:

****
var startsWith = "AND";
var plans = model.Plans.Where(p => p.plan.StartsWith(startsWith))
.Select(p => new
{
p.plan,
p.id
}).ToList();
****

And an error is thrown by the database (specifically: "ERROR 42000 IBMIDS/UNIX64 A syntax error has occurred."). Weird. So I check out the generated SQL:

****
SELECT Extent1.id AS id,
Extent1.plan AS plan
FROM "informix".Plans AS Extent1
WHERE Extent1.plan LIKE 'AND%' /* @p__linq__0 */ ESCAPE CAST('\' AS nvarchar(1))
****

Instead of casting 'AND%' as nvarchar(4), it tries to escape cast a single character?

Why does this happen? I appreciate the ability to do L2E .StartsWith() queries, but it doesn't do us any good if I can't use a variable as the parameter to .StartsWith().
Updated on 2012-03-30T18:06:54Z at 2012-03-30T18:06:54Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2826 Posts
    ACCEPTED ANSWER

    Re: v9.7fp4 syntax error with .StartsWith() in LINQ to Entities

    ‏2011-05-31T00:00:54Z  in response to kdawg
    Hi,

    thank you for reporting this issue. The problem seems to be centered around the fact that IDS server doesn't allow CAST expression to be used in ESCAPE clause of the LIKE predicate. I.e. the last where clause should look like this:

    
    WHERE Extent1.plan LIKE @p__linq__0 ESCAPE 
    '\'
    


    It is a problem with our SQL generator and it will be addressed in the future releases.

    Thanks,
    Alex
    • kdawg
      kdawg
      9 Posts
      ACCEPTED ANSWER

      Re: v9.7fp4 syntax error with .StartsWith() in LINQ to Entities

      ‏2011-06-30T20:52:51Z  in response to SystemAdmin
      Not a problem! Thanks for replying to my post!
    • SystemAdmin
      SystemAdmin
      2826 Posts
      ACCEPTED ANSWER

      Re: v9.7fp4 syntax error with .StartsWith() in LINQ to Entities

      ‏2012-03-30T17:21:30Z  in response to SystemAdmin
      Doesn't appear to be fixed with FP5.
      • SystemAdmin
        SystemAdmin
        2826 Posts
        ACCEPTED ANSWER

        Re: v9.7fp4 syntax error with .StartsWith() in LINQ to Entities

        ‏2012-03-30T18:06:54Z  in response to SystemAdmin
        Sorry it does work. My bad.

        Thanks!