Comparison operators not supported for type 'S

问题:

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.

回答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));

回答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.

回答3:

It's probably the case that context.Request.QueryString["q"] returns a string array instead of a single string. This is because a url may contain multiple parameters with the same name.

If you are sure there is always just one parameter named q on the request, you can change your code to this: context.Request.QueryString["q"].SingleOrDefault().

回答4:

Firstly, I'd avoid storing delimited data in the database. As you've found out it can make database queries etc awkward.

If you have a limit amount of categories, I'd go with Joe's answer for simplicity, else, adding some detail to Marc's approach.

1. Create a split-style UDF, for SQL Server I use:

CREATE FUNCTION FN_CHARLIST_TO_TABLE
 (@list      nvarchar(MAX),
  @delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
              str     varchar(4000)      NOT NULL,
              nstr    nvarchar(2000)     NOT NULL) AS
/*                
  Comments:
        - Takes a CSV string, and creates a table of data from this
        - Each item takes one row in the following format
            listpos - the index of the item in the string (effectively a row number in the output)
            str - The value, as VARCHAR
            nstr - The value, as NVARCHAR

        - This function is a direct extract from http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings
Usage:
    SELECT * 
    FROM  t 
    JOIN FN_CHARLIST_TO_TABLE('a,b,c,1,2,3', ',') list
    ON t.Name = list.str        
*/
BEGIN
   DECLARE @endpos   int,
       @startpos int,
       @textpos  int,
       @chunklen smallint,
       @tmpstr   nvarchar(4000),
       @leftover nvarchar(4000),
       @tmpval   nvarchar(4000)

   SET @textpos = 1
   SET @leftover = ''
   WHILE @textpos  0
      BEGIN
     SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
                         @endpos - @startpos - 1)))
     INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
     SET @startpos = @endpos
     SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2,
                 @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   END

   INSERT @tbl(str, nstr)
      VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
END

2. Add this to your DBML file
3. Query your database, referencing the UDF appropriately

var catergories = from cat in _datacontext.Categories
                  from keyword in _datacontext.FN_CHARLIST_TO_TABLE(cat.Keywords, ' ')
                  where keyword.str == context.Request.QueryString["q"]
                  select cat;

This will then execute a purely database-side query.

回答5:

1) ToList() your var query

2) In generated list in 1 step write your contain and split query: it execute correctly

good luck