linq to entity Contains() and nested queries

Question

Hello !

i've a trouble with linq, i'll explain on example :

i have a database table called Employee which got FirstName and LastName columns, and a method to search employees which gets a nameList list as argument, the elements in this list are names formatted like this one "Fred Burn", or this1 "Grim Reaper", already tryed these approaches with no luck =[

//just all employees
var allData = from emp in Context.Employee select emp;

var test1 = from emp in allData
            where(emp.FirstName + " " + emp.LastName).Contains
            ("" + ((from n in nameList select n).FirstOrDefault()))
            select emp;

var test2 = (from emp in allData
             where (emp.FirstName + " " + emp.LastName)
             == ((from n in nameList select n).FirstOrDefault())
             select emp);

var test3 = from emp in allData
            where (from n in nameList select n).Contains
            (emp.FirstName + " " + emp.LastName)
            select emp;

first and second queries give : {"Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."} exception

and third : {"LINQ to Entities does not recognize the method 'Boolean ContainsString' method, and this method cannot be translated into a store expression."}

would be glad to hear your suggestions :)

Thank You!

p.s.
yea i know it's possible to split names in list and compare them separately, but still curious why wont these queries work.

Answer 1

I assume nameList in this case is an in memory collection and that you are trying to use the LINQ to SQL trick creating a SQL "IN" clause inside of the Entity Framework. Unfortunately, EF doesn't support this syntax (yet). Depending on the number of records you are trying to match, you may be able to run separate queries for each child you are desiring. Alternatively, you could build an entitysql query using concatenation to append the multiple items from the nameList as separate OR clauses in the WHERE operation.