LINQ to Entities does not recognize the method 'System.String [] Split (Char [])',

Question

I am trying to implement a method in which keywords stored in the database for activity (separated by a comma) match a given string, separated by a comma.

public List<TblActivities> SearchByMultipleKeyword(string keywords) { string[] keyword = keywords.Split(','); var results = (from a in Entities.TblActivities where a.Keywords.Split(',').Any(p => keyword.Contains(p)) select a).ToList(); return results; } 

I get the following error:

 LINQ to Entities does not recognize the method 'System.String[] Split(Char[])' method, and this method cannot be translated into a store expression. 

Answer 1

For queries that do not include too many keywords and too many lines, you can implement this simple and quick solution. You can easily bypass the Split function by repeatedly refining your results as follows:

  public List<TblActivities> SearchByMultipleKeyword(string keywords) { string[] keywords = pKeywords.Split(','); var results = Entities.TblActivities.AsQueryable(); foreach(string k in keywords){ results = from a in results where a.Keywords.Contains(k) select a; } return results.ToList(); } 

Answer 2

You cannot do this using the Entity Framework, as the error message says.

However, there are options.

One option is to understand that if keywords are stored as A,B,C,D , then x is there if

 a.Keywords.StartsWith(x + ",") || a.Keywords.Contains("," + x + ",") || a.Keywords.EndsWith("," + x) 

This works if x does not contain,. The disadvantage is that it will do a full scan of the table or index containing the Keywords column.

Another option is to normalize your database. In the end, you have a relationship between one action and a keyword. Then simulate it as such: in addition to the Activities table (without the Keywords column), enter a Keywords table with two columns, a foreign key in the action table, and a keyword column. This will allow you to add an index to the keyword column, which can make the query very fast.

UPDATE

I re-read your question and noticed that you are not testing keyword equality, but just Contains . If so, why don't you just do the following?

 a.Keywords.Contains(x) 

Answer 3

String.Split not supported by the Entity Framework. This is simply because there is no equivalent in SQL.

Decision:

• to define a user-defined function in a database This article offers several solutions: http://sqlperformance.com/2012/07/t-sql-queries/split-strings
• to declare this function applicable LINQ to objects using the [EdmFunction] attribute, as described here: How to call the DB function from an EF LINQ query?

Answer 4

LINQ to Entities is trying to translate your LINQ query into SQL. Since he does not know how to make String.Split in an SQL query, he fails.

This means that if you do not want to write an SQL implementation of String.Split , you can only do this in LINQ for objects, which means that you need to first load all your data into memory, and then make a where clause. One easy way to do this is to use .ToList() :

 var results = (from a in Entities.TblActivities select a).ToList(); //Results are now in memory results = results.Where(a => a.Keywords.Split(',').Any(p => keyword.Contains(p))).ToList(); //This uses LINQ-to-objects 

Answer 5

Yes, you can do it like this:

 public List<TblActivities> SearchByMultipleKeyword(string keywords) { string[] keywordsSeparated = keywords.Split(','); var results = (from a in Entities.TblActivities where keywordsSeparated.Any(keyword => a.Keywords.Contains(keyword)) select a).ToList(); return results; } 

Answer 6

Not sure, but you can try: Since the error seems to be looking for an array, this might work.

 string[] keyword = keywords.Split(new char[] {','}); var results = (from a in Entities.TblActivities where a.Keywords.Split(new char[] {','}).Any(p => keyword.Contains(p)) select a).ToList();