Query exception - SQL CE3.5

Hi,

I am trying to execute the following command to search for and existing record with the title name as below, "Title" is setup as a 'String' data type in the Lightspeed Entity property box.

var existingEntry = uow.Adns.Single(o => o.Title == "transport");

however when I execute that line of code I get the InnerException message as follows:

"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 have also tried using other query methods such as below, however they are failing with the same exception message as above

var existingEntry = uow.FindOne((Entity.Attribute("Title") == "transport") );

Could you please advise what might be the problem and how can I resolve this? The database I am using is SQL CE 3.5 (version 3.5.8080.0). Thanks!

The problem is that as you are storing an unbounded string the storage for this in SQL CE needs to be a NTEXT column, and as you can see from the error SQL CE does not support NTEXT being used in WHERE, HAVING, GROUP BY or IN clauses. What you will want to do is set a length restriction on your Title property so that an NVARCHAR(length) column is generated instead which will not be subject to the same restrictions :)

Jeremy

Thanks Jeremy for your reply! Does that means I really can't be using datatype of "string" for any of my entity fields(whenever I am using SQL CE), and all of them need to be reconfigured to be NVARCHAR(length) in order to be assessible by query?

If so could you please let me know the best method/implementation example for me to change all the string fields to be NVARCHAR(length), perhaps a method to do it universally for all my entities in my database model, as well as a method to change it on an individual field basis? I am new to Lightspeed and database programming, so your guidance on this is much appreciated! Thank you!

No you definitely can use a String but you will need to apply a custom length validation to trigger the designer to use a NVARCHAR(size) data type rather than TEXT when updating the schema. If you update the schema manually you can bypass this but it is a good idea to keep your model in sync with your schema in regards to any length restrictions for storage since you may inadvertently end up with truncated data if you dont set this.

So you will want to keep the data type as string and then set a validation for Length on the entity property - e.g.

Jeremy

Thanks Jeremy for helping to clarify the situation and also advice of a best method solution to implement what I require. The code is working now after the changes made as advised : ) ! Thank you very much!