9

In my application's search function, I have implemented a search function which executes the following statement against the database.

resultsquery = db.DBMovies.Where(m => (m.Actors.ToLower()).Contains(q.ToLower()))

In the part q.ToLower() I read the url parameter q and converts into lowercase and find it in the relevant database column. In my database, the column 'Actors' is of type 'text' rather than varchar. When I run my application, I get an exception called Argument data type text is invalid for argument 1 of lower function. Is there any way that I can avoid this exception? I prefer a way that I can solve it in a single line.

Thank you.

Christos
  • 53,228
  • 8
  • 76
  • 108
Deepal
  • 1,729
  • 5
  • 24
  • 34
  • 1
    text is sql datatype for very large texts, it can hold up to 2GB; I sincerely doubt you'll need that for actor column of "Movie" table; you really should be using nvarchar(some reasonable limit) or nvarchar(max) if you're unreasonable - check out http://technet.microsoft.com/en-us/library/ms187993.aspx – Ondrej Svejdar Mar 27 '14 at 10:34
  • text and ntext fields are for very large text data. You should use a varchar field. You can perform queries like that easier against varchar fields than text fields. For more info see: http://databases.aspfaq.com/database/why-do-i-get-argument-data-type-text-is-invalid-for-argument.html – Donal Mar 27 '14 at 10:37
  • 1
    check if this can help you http://stackoverflow.com/questions/2431908/linq-to-entities-using-tolower-on-ntext-fields – Manoj Naik Mar 27 '14 at 10:37
  • @OndrejSvejdar You are right! but isn't there a way to solve this without changing database? – Deepal Mar 27 '14 at 10:37

2 Answers2

8

Just change the datatype of your column in database to NVARCHAR(MAX) and your code will work like a charm. It's a known issue, if you do some googling. For instance please have a look here.

Christos
  • 53,228
  • 8
  • 76
  • 108
1

You can simply convert the column to a VARCHAR - Something like this:

ALTER TABLE DBMovies
ALTER COLUMN Actors VARCHAR(MAX)
Donal
  • 31,121
  • 10
  • 63
  • 72