Issue with Linq-to-Entities, parameters and DB2 9.5.3

Question

First, some background info about the environment...

There are two machines: Windows “client” and Linux “server”.

1. Windows 2008 R2 x64 • IIS 7.5 • NET 3.5 • SharePoint 2010 • Visual Studio 2010 • IBM DB2 .NET Data Provider Runtime Version v4.0.30319, driver version 9.7.4.4.
2. RedHat Linux, IBM DB2 database version 9.5.3*.

*The recommended solution outlined on IBM’s website is to upgrade the client to 9.7 FP5. I have tried this and even upgraded to version 10 but still got the same error. If the author of this article was referring to the actual database version running on the server needing to be upgraded then that poses a problem as this is not an acceptable solution to my customer at the present time.

I am not dead set on using LINQ-to-Entities and would welcome compatible alternatives provided I wouldn’t be required to rewrite huge swaths of existing code that currently depend on it.

Now, on to the issue…

Below is the code I’m trying to execute. Note caseid parameter used in the query.

public List<JqueryAutocompleteEntity> Cases(string caseid)
{
    var query = (from c in context.ASSET_T
                             where c.CA_ID_AGCY.StartsWith(caseid.Trim().ToUpper())
                             select new JqueryAutocompleteEntity
                                        {
                                            id = c.CA_ID_AGCY.Trim(),
                                            label = c.CA_ID_AGCY.Trim(),
                                            value = c.CA_ID_AGCY.Trim()
                                        }).Distinct().Take(10);
    return query.ToList();
}

When the code above is executed at runtime the following exception is thrown:

An error occurred while executing the command definition. See the inner exception for details. IBM.Data.DB2.DB2Exception: ERROR [42815] [IBM][DB2/LINUXX8664] SQL0171N The data type, length or value of the argument for the parameter in position "string-expr" of routine "SYSIBM.STRIP" is incorrect. Parameter name: "". at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg, Boolean skipDeleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation) at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method) at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior) at IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

This is the SQL statement generated by LINQ-to-Entities that is passed to the .NET DB2 drivers for execution on the Linux server in the DB2 database.

SELECT CAST(1 AS int) AS C1, STRIP(Extent1.CA_ID_AGCY,BOTH) AS C2, STRIP(Extent1.CA_ID_AGCY,BOTH) AS C3, STRIP(Extent1.CA_ID_AGCY,BOTH) AS C4 FROM ASSET_T AS Extent1 WHERE (LOCATE(UPPER(STRIP(@p_linq_1,BOTH)), Extent1.CA_ID_AGCY)) = CAST(1 AS int)

As you can see, it appears that the name of the parameter/variable was used when generating the final SQL statement instead of substituting it with the actual value. In addition, hard coding a value into my LINQ query actually works fine.

c.CA_ID_AGCY.StartsWith(“test”.Trim().ToUpper()) <= This works!!!

I've already tried using the .AsEnumerable() extension in the first part of the LINQ query so I can load all the data into memory and then perform the filter using my parameter but this approach obviously didn't work because the resultset was WAY too large (i.e. millions of records). It ends up killing the server's CPU and maxes out the RAM. I can obviously accomplish what I need with a simple "tried and true" DataReader/DataTable approach by executing the query directly against the database and using a foreach loop to populate a collection of JQueryAutocompleteEntity objects but I'd prefer to use a more elegant solution like LINQ-to-Entities and also avoid wasting additional execution time populating the list in the foreach loop.

What I find so troubling about all this is that support for parameterized queries seems like such an obvious feature. I mean how often do you use constants over variables when coding in any language?

I think Scott Guthrie's blog post about his custom dynamic query library appears to provide a workaround to my issue but just isn't quite there.

Any assistance in resolving this issue (without the need to upgrade the .NET Framework or DB2) would be much appreciated.

Thanks!