LINQ to Entities: Why can't I use Split method as condition?

Question

I have the following LINQ query:

var aKeyword = "ACT";
var results = from a in db.Activities
              where a.Keywords.Split(',').Contains(aKeyword) == true
              select a;

Keywords is a comma delimited field.

Everytime I run this query I get the following error:

"LINQ to Entities does not recognize the method 'Boolean Contains[String](System.Collections.Generic.IEnumerable`1[System.String], System.String)' method, and this method cannot be translated into a store expression."

What is the alternative for what I am trying to do?

Answer 1

Your problem is that LINQ-to-Entites has to translate everything you give it into SQL to send to the database.

If that is really what you need to do, you'll have to force LINQ-to-Entities to pull back all the data and LINQ-to-Objects to evaluate the condition.

Ex:

var aKeyword = "ACT";
var results = from a in db.Activities.ToList()
              where a.Keywords.Split(',').Contains(aKeyword) == true
              select a;

Be aware though, that this will pull back all the objects from the Activities table. An alternative may be to let the DB do a bit of an initial filter, and filter down the rest of the way afterwards:

var aKeyword = "ACT";
var results = (from a in db.Activities
              where a.Keywords.Contains(aKeyword)
              select a).ToList().Where(a => a.KeyWords.Split(',').Contains(aKeyword));

That will let LINQ-to-Entities do the filter it understands (string.Contains becomes a like query) that will filter down some of the data, then apply the real filter you want via LINQ-to-Objects once you have the objects back. The ToList() call forces LINQ-to-Entities to run the query and build the objects, allowing LINQ-to-Objects to be the engine that does the second part of the query.