c# - 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.

Answer 1

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)