Query tests fail with SQLCE provider

[ErikEJ]

The test String_EndsWith_MethodCall() fails with

  System.FormatException : @__LocalMethod2_0 : m - Input string was not in a correct format.
at System.Data.SqlServerCe.SqlCeCommand.FillParameterDataBindings(Boolean verifyValue)

The 2 other tests with the LIKE statements similar to this also fail.

LIKE '%' + @__LocalMethod2_0

I think this is due to a limitation in the SQLCE engine, as I have alluded to previously http://erikej.blogspot.dk/2013/06/sql-server-compact-code-snippet-of-week_23.html

(A fix would be that the @__LocalMethod2_0 parameter value was "%M" )

Is this something I need to fix in the SQLCE EF provider? And in that case any pointers to how?

[divega]

Interesting. Are you sure it isn't another type inference limitation in SQL CE? Just asking given our long history with those :smile: (e.g. CodePlex #287 and its fix and the more recent #2317).

It may very well be another kind of limitation but just thinking that the type, size and nullability of the parameter could have an incidence. Does concatenation between a string constant and a string parameter work outside of LIKE?

[ErikEJ]

@divega thanks for great input, I will do some testing!

[ErikEJ]

@divega Spot on! Still an issue that the CommandBuilder does not set the DbType. Adding this line in my test makes the query pass:

parameter.DbType = System.Data.DbType.String;

Looks like in the MethodCall instance, the StoreType is not set, so no DbType is set in the RelationalTypeMapping class

        if (StoreType.HasValue)
        {
            parameter.DbType = StoreType.Value;
        }

Is this something I need to improve in the SQLCE provider, and in that case, how?

[divega]

Awesome! I suspect we can change things in Core and Relational so that DbType is set in this case and in general in more cases. @ajcvickers would know for sure if that makes sense.

[divega]

BTW, you might want to provide a new answer to this question in StackOverflow: http://stackoverflow.com/questions/1916248/how-to-use-parameter-with-like-in-sql-server-compact-edition

Let me know and I will upvote it :smile:

[ErikEJ]

@divega This is now fixed in the SQLCE provider, I had a bug (not being explict enough in my TypeMapper) - thanks for the help!

[divega]

Glad it worked, and thank you Erik!

[ajcvickers]

@divega Just to clarify, setting the DbType is something that is entirely up to a provider. This is because always setting the DbType doesn't always work, never setting doesn't always work, and the types that need it set vary per provider. So a provider must create a type mapping either with a DbType or without a DbType. I assume @ErikEJ is now creating a type mapping in the CE provider with the DbType set and hence it is now working.

[ErikEJ]

Yes, I am massaging the Parameters quite a lot, to Work around a number of SQLCE quirks:

https://github.com/ErikEJ/EntityFramework.SqlServerCompact/commit/3a713ebf3bdcd2a4571124509785295d28587c38#diff-f84e58fb27753166b396cf85478f7101

https://github.com/ErikEJ/EntityFramework.SqlServerCompact/commit/3a713ebf3bdcd2a4571124509785295d28587c38#diff-f88fa5efbceb344ee1dd9d3ae293034d

[divega]

@ajcvickers Thanks for the explanation.