Accepted answer

Take a look at this tip, it is not exactly what you are asking for but I think you can tweak it to get what you want.



Try this:

int i = 0;

bool exists = StringOfInts.Split(',').Any(
                s => Int32.TryParse(s, out i) && 
                     listOfInts.Any(n => n == i)


This will do the trick...

 List<int> integerList = new List<int>() { 1, 2, 3, 4 };
 string integersAsString = "1,3,5";

 var result = integersAsString.Split(',')
                             .Select(s => Int32.Parse(s))
                             .Where(i => integerList.Contains(i));

 foreach (var i in result)
    Console.WriteLine(i); // prints 1 and 3


As you realized, that query cannot be translated to SQL, so EF will refuse to translate the query.

You will need to split the query between your code and the database, by fetching the delimited strings and then checking them in the client:

query = query.Select(x => x.DelimitedStringOfInts)
            .ToList() // Run the query in the database now
            .Where(ints => ints.Select(s => int.Parse(s)).Any(listOfInts.Contains));

If you prefer to run everything in the database, I think you'll have to use sprocs or raw SQL for this, or dynamically build a query expression, like that suggested in Alex James' answer.

You can also speed up the client portion with the HashSet idea from Jimmy's answer.

And, as another idea (no idea if you can do it) but if there is any possibility of extending EF's QueryProvider, that would make it possible to translate a call to List.Contains to an SQL WHERE ... IN .... Might not be easy. EF 4 will have that built-in, by the way.


convert the string to a HashSet for optimum performance of .Contains. .Any() should return true when the first match is found.

 var stringofInts = "2,3,5,9";
 List<int> listOfInts = GetSomeListOfInts();

 var set = new HashSet<int>(stringofInts.Split(',').Select(x => int.Parse(x)));
 listOfInts.Any(x => set.Contains(x))


DelimitedStringOfInts.Split(new char[]{','})
                     .Select<string, int>(s => int.Parse(s))
                     .Intersect(listOfInts).Count<int>() > 0

Related Query

More Query from same tag