SQLCE ntext support in query pipeline

[ErikEJ]

With the latest bug fixes in the SQLCE typemapper, the EF UPDATE pipeline now supports ntext and image columns. But due to the SQL CE limitations regarding ntext, it is sadly not possible to write a query like this based on the Northwind Categories table (where Description is "ntext NULL"):

SELECT [Category Name]
FROM [Categories]
WHERE Description = 'Seaweed and fish';

This causes the following error:

 Error Code: 80040E14
 Message   : The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses, except when these data types are used with the LIKE or IS NULL predicates.
 Minor Err.: 25923
 Source    : SQL Server Compact ADO.NET Data Provider 

However, rewriting the query like this works as expected:

SELECT [Category Name]
FROM [Categories]
WHERE CAST(Description AS nvarchar(4000)) = 'Seaweed and fish'

So my question is, would it be possible for me to intercept during SQL generation, and if so, where? I would only want to rewrite for ntext columns, of course, as this rewrite prevents use of any indexes.

[divega]

@ErikEJ I will leave a more detailed answer on how to override the SQL generation for this case to @anpete.

What is the default type mapping for a string property with no MaxLength specification in your provider? If you haven't done this yet, I would suggest you consider compensating for this limitation at model creation time rather than at query time. E.g. you could have the type mapping for string properties with no MaxLength specification default to nvarchar(4000) columns instead of an ntext columns.

Users could still explicitly ask for the column to be an ntext or text but then I think it would be acceptable if they get errors from SQL CE when they try to use those in ways that aren't supported by the SQL CE engine.

FWIW, this is similar to what we did in Code First for EF6 (we used a SQL CE specific convention) and I don't remember anyone complaining :smile:

[ErikEJ]

@divega I have done that already. https://github.com/ErikEJ/EntityFramework.SqlServerCompact/blob/master/src/SqlCeTypeMapper.cs#L23 Will just let the enigine error if doing compares on ntext columns (text is not supported by SQLCE, only Unicode types)