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

Question

I am trying to implement a method where the keywords stored in the database for an activity (split by a comma) match the giving string split 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 am getting 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.

Solution 1

For queries that do not involve too many keywords and too many rows you could implement this simple and quick solution. You can easily get around 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();
 }

Solution 2

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

However, there are options.

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

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

That works if x does not contain , itself. The downside is that this will do a full scan of the table, or of an index containing the Keywords column.

The other option is to normalize your database. After all, you have a one to many relationship between activity and keyword. Then model it as such: in addition to an Activities table (without the Keywords column), have a KeyWords table with two columns, a foreign key to your activities table, and a keyword column. This will allow you add an index on the keyword column, which can make the query super-fast.

UPDATE

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

a.Keywords.Contains(x)

Solution 3

Yes you can do it like that:

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;
}

Solution 4

String.Split isn't supported by Entity Framework. This is simply because there is no equivalent in SQL.

A solution is:

1. to define a custom function in the database This article proposes several solutions: http://sqlperformance.com/2012/07/t-sql-queries/split-strings
2. to declare this function usable by LINQ to Entities using the [EdmFunction] attribute like explained here: How to call DB function from EF LINQ query?

View more solutions