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

f#, query-expressions

Solution

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

Problem

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 } ```

Original source