How do I do a "contains" query with f# query expressions?

Question

How do I do a query expression similar to a SQL IN-query?

I'm trying to do something along these lines:

let customerNumbers = set ["12345"; "23456"; "3456"]
let customerQuery = query {
    for c in dataContext.Customers do
    where(customerNumbers.Contains(c.CustomerNumber))
    select c
}

But I'm getting an error:

System.NotSupportedException: Method 'Boolean Contains(System.String)' has no supported translation to SQL.

Looking at the documentation for query expressions at http://msdn.microsoft.com/en-us/library/hh225374.aspx I should use another query for the contains part but this code doesn't work, the example is broken:

// Select students where studentID is one of a given list.
let idQuery = query { for id in [1; 2; 5; 10] do select id }
query { 
    for student in db.Student do
    where (idQuery.Contains(student.StudentID))
    select student
}

idQuery does in fact not contain any "Contains" method.

I have also tried:

let customerNumbers = set ["12345"; "23456"; "3456"]
let customerQuery = query { 
    for c in dataContext.Customers do
    where (query { for x in customerNumbers do exists (c.CustomerNumber=x)})
    select r
}

But this gives this error message:

System.NotSupportedException: Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator

I noticed after some more testing that the following also works fine in addition to Gene's suggestion:

let customerNumbers = set ["12345"; "23456"; "3456"]
query {
    for customer in dataContext.Customer do
    where (query { for x in customerNumbers do contains customer.CustomerNumber})
    select customer
}

Messinger

I just noticed that my first example seems to work if I replace the set with a list instead, oddly enough. – Messinger

Would you mind including the full stack traces for the exceptions you're getting? It won't help answer your question, but I'm curious to see if it's breaking in the F# query -> LINQ translation layer, or in the LINQ -> SQL translation. – Bastia

@JackP: here you go. – Tyrannize

This is supported in SQLProvider: github.com/fsprojects/SQLProvider – Lodhia

Answer 1

The problem I believe comes from the way F# Set implements method Contains. It belongs to ICollection interface and this fact somehow upsets LINQ-to-SQL query builder.

If you explicitly force your Contains into the extension method of IEnumerable territory everything gets OK:

let customerNumbers = set ["12345"; "23456"; "3456"]
let customerQuery = query {
    for c in dataContext.Customers do
    where((customerNumbers :> IEnumerable<string>).Contains(c.CustomerNumber))
    select c
}

Or, equivalently, you can add non-LINQ-to-SQL query

let idQuery = query { for id in customerNumbers do select id }

which has no problems with enumerating set yielding seq<string> and then use it for Contains as

....
where (idQuery.Contains(c.CustomerNumber))
....

Or, to begin with, you may keep your customerNumbers as seq:

let customerNumbers = set ["12345"; "23456"; "3456"] |> Set.toSeq

and use it as intuition prompts:

....
where(customerNumbers.Contains(c.CustomerNumber))
....

Tyrannize