SQL CE 4 Error: The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses

Question

I'm trying to migrate an old SQL Server database (created in Entity Framework 4) to SQL CE 4. I've moved everything, recreated the schema and inserted the data into the SDF file, but now when I try to launch my site, I'm getting this error:

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.

I've visited several links and they all link to a Microsoft hotfix, which is for SQL CE 3.5. I'm running SQL CE 4 and couldn't find anything relevant.

UPDATE: I've found some possible solutions for Code-first approach, but my database is model-first.

but I have blog entries that are over 4000 characters (which can be arbitrarily long) — Can Poyrazoğlu, Dec 14 '12 at 13:01
Did you find an answer? I ran into the same problem with SQL CE 4 — Pepedou, Jul 26 '17 at 03:59
@Pepedou unfortunately I don't remember. I've abandoned the project. — Can Poyrazoğlu, Jul 26 '17 at 04:10

score 1 · Answer 1 · answered Jun 19 '13 at 08:53

I just ran into this problem with Entity Framework and SQL CE. It can't generate a dynamic SQL statement that filters the results by an image column.

For me, this code was failing (token being of type byte[]):

var dataStore = new DataStore();
var document = dataStore.Documents.SingleOrDefault(d => d.Token == token);

The simple way that I got it working was to first load the documents into a list in memory, before filtering it. This is fine for me because the list is never going to be very large.

var dataStore = new DataStore();
var documents = dataStore.Documents.ToList();
var document = documents.SingleOrDefault(d => d.Token.SequenceEqual(token));

SQL CE 4 Error: The ntext and image data types cannot be used in WHERE, HAVING, GROUP BY, ON, or IN clauses

1 Answers1