3

Closed

SQLCE provider doesn't specify type for string and binary parameters

description

This issue blocks http://entityframework.codeplex.com/workitem/145

Scenarios like these:

using (var ctx = new SqlCeIssueEntities())
{
ctx.ContextOptions.UseCSharpNullComparisonBehavior = true;
var bar = "bar";
var query = ctx.CreateObjectSet<Foo>().Any(f => f.Bar == bar);
foreach (var foo in query)
Console.WriteLine(foo.Id);
}

using (var ctx = new SqlCeIssueEntities())
{
var queryString = @""select value c from SqlCeIssueEntities.Tests as c where @myVar is null or c.name = @myVar"";
ObjectQuery<Test> contactQuery = new ObjectQuery<Test>(queryString, ctx);
contactQuery.Parameters.Add(new ObjectParameter(""myVar"", ""TestVal""));
foreach (Test result in contactQuery)
Console.WriteLine(""Last Name: {0};"", result.name);
}

Currently fail with an exception like this:

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details.
System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlServerCe.SqlCeException: The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = isnull ]
at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlServerCe.SqlCeMultiCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
...


The underlying issue is as follows:

EF doesn’t provide enough facets for parameters so that the SQLCE provider is forced to guess the appropriate types for strings and byte[]. Originally SQLCE was simply guessing that ntext and image were the most appropriate types (because they can hold the whole range of data sizes as the CRL parameters), however these are not comparable and a simple predicate like ‘WHERE table.Column = @parameter’ would fail.

SQLCE team then made a change in which they stopped setting the type of string and binary parameters. This allows SQLCE's query processor to infer an appropriate type from the SQL string when there is a comparision between a string parameter and a string column, e.g.:

... WHERE f.Bar = @bar

However inference is not possible in certain cases, e.g. when there is an IS NULL comparison against a strin parameter for which the type hasn't been specified:

... WHERE f.Bar = @bar OR @bar IS NULL

We believe that this issue could be fixed by preserving the type parameter, but infering lenght facets from usage, similar to when we infer a string parameter should be non-unicode if it is compared with a non-unicode column. Another alternative we should investigate is introducing explicit casts in the query for SQL CE, e.g.

... WHERE f.Bar = @bar OR cast(@bar AS varchar(4000)) IS NULL
Closed Mar 7, 2013 at 9:34 PM by BriceLambson

comments

BriceLambson wrote Mar 7, 2013 at 8:07 PM

Fixed in changeset 9b448466473c (by Emil)

BriceLambson wrote Mar 7, 2013 at 9:34 PM

Verified