Topic
  • 4 replies
  • Latest Post - ‏2012-03-30T18:06:54Z by SystemAdmin
kdawg
kdawg
9 Posts

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

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

    ‏2011-05-31T00:00:54Z  
    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

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

    ‏2011-06-30T20:52:51Z  
    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:

    <pre class="jive-pre"> WHERE Extent1.plan LIKE @p__linq__0 ESCAPE '\' </pre>

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

    Thanks,
    Alex
    Not a problem! Thanks for replying to my post!
  • SystemAdmin
    SystemAdmin
    2826 Posts

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

    ‏2012-03-30T17:21:30Z  
    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:

    <pre class="jive-pre"> WHERE Extent1.plan LIKE @p__linq__0 ESCAPE '\' </pre>

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

    Thanks,
    Alex
    Doesn't appear to be fixed with FP5.
  • SystemAdmin
    SystemAdmin
    2826 Posts

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

    ‏2012-03-30T18:06:54Z  
    Doesn't appear to be fixed with FP5.
    Sorry it does work. My bad.

    Thanks!