Sometimes you have to wonder how people miss things.. Something simple like:
string movieTitle = "Ghostbusters";
var movies = from mov in mdb.Movie
where mov.Title.Contains(movieTitle)
select mov;
Fails with a nasty 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"
This only happens with the ADO.Net Entity Framework running against the SQL Server Compact Edition (3.5) and only since RTM (+ SP 1).
The workaround is to form your query like so:
string movieTitle = "Ghostbusters";
var movies = from mov in mdb.Movie.Where("it.Title like ‘%" + movieTitle + "%’")
select mov;
According to Ravi Tandon [MSFT]:
"another issue got crept in where the parameter that you pass gets type casted as ntext instead of nvarchar, thats why you see this error.
Until we resolve the issue, I can suggest you following work-arounds:
1. This one seems to be the best amongst the worse. Substitute the value of the string in the Where expression. Something like this. Move the where clause with the customers, use string concatenation to generate the query with the value subsituted instead of using parameter.
string str = "Sales Representative";
var query = from cust in context.Customers.Where("it.ContactTitle == ‘" + str + "’")
select cust;
2. Which you are already aware of, i.e. instead of parameters use direct values in queries like "foo". But that does not solve all isues. And above one is better than this if you want to do this in a loop and to avoid hard-coding things.
3. This is worst-performance work-around. Select all objects and inside your foreach/for loop use a if condition to work on only those objects. Here you will have to go over all the entities. Not good if number of entities are huge."
Source: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3746251&SiteID=1
