LINQ (to Entities) - Filtering items using ints

Question

Using LINQ to Entities, how can I determine if any item from a List of ints exists in a comma delimited string of ints?

For example, I want to write something like the following (logically):

collection.Where(collection.DelimitedStringOfInts.Contains(listOfInts.AnyOfThem))

Also, I should mention that I'm doing this using LINQ method chaining, with the delimited string as part of the entity -

var listOfInts = GetListOfInts();
var query = from x in Db.Items select x;
if (listOfInts != null && listOfInts.Count() > 0)
{
  query = query.Where(x => x.DelimitedStringOfInts.Contains(listOfInts.AnyOfThem));
}

UPDATE:
Using Alex's referenced article, I implemented a working solution as follows:

var query = from x in Db.Items select x;
var listOfInts = GetListOfInts();
if (listOfInts != null && listOfInts.Any())
{
    //"ToListOfStrings" is a simple extension method I wrote to create a List<string> from a List<int>
    var delimitedIds = listOfInts.ToListOfStrings(','); 
    query = query.Where(
     BuildOrExpression<DatabaseItem, string>(x => x.DelimitedStringOfInts, delimitedIds)
     );
}

An update was required to the "BuildOrExpression" referenced in the article. The "equals" operator had to be changed to a "contains" operator.

var contains = values.Select(value =>
    (Expression)Expression.Call(
          valueSelector.Body,
          typeof(string).GetMethod("Contains"),
          Expression.Constant(
            value,
            typeof(TValue)
          )
        )
   );

Answer 1

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.

Alex

Answer 2

Try this:

int i = 0;

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

Answer 3

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

Answer 4

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))

Answer 5

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

Answer 6

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.