Comparison operators not supported for type 'System.String[]'

Question

why this line:

var category = _dataContext.Categories.Where<Category>(p => p.Keywords.Split(' ').Contains<string>(context.Request.QueryString["q"])).First();

throws an System.NotSupportedException:

Comparison operators not supported for type 'System.String[]'

And how can I fix it? Thanks.

Answer 1

So you are looking for a value (from the query-string) in a space-delimited column in the database? And you're using Split to query the individual values inside the database?

(just checking my assumptions...)

string.Split is not supported in this way (at the database on column data) - see here for the supported string operations. (note that string.Split is explicitly not supported).

I'm lazy; when I delimit data in the database (relatively rare), I always add the same delimiter to the start and end of the data; then I can just search for:

string searchFor = DELIMITER + searchValue + DELIMITER;
...
.Where(row => row.Value.Contains(searchFor));

However; in this case, I expect the most practical option might be to write a UDF function that searches a delimited varchar (correctly handling the first/last item), and expose the UDF on the data-context - then use:

.Where(row => ctx.ContainsValue(row.Value, searchValue)); // ContainsValue is our UDF

Or - normalise the data...

.Where(row => row.Values.Any(s=>s.Value == searchValue));

Answer 2

string.split is not supported in LINQ-to-SQL.

There's an easy fix. Select all the data and do the filtering in the client. This may not be very efficient depending on the number of categories.

var category = 
    _dataContext.Categories.ToList()
    .Where<Category>(p => p.Keywords.Split(' ').Contains<string>(context.Request.QueryString["q"])).First();

Calling .ToList() will force enumeration of all the categories from your datasource, and the subsequent operations will be performed in the client code.