FLinq SQL "IN Clause"

Question

I am trying haplessly to create an "IN CLause" through f# linq - but can't seem to get it. I have tried two options:

Iterating over the list of days as part of the sequence expression:

let getHistoricalQuotes (securityId:int) (days:string seq) =
    let results = 
        Query.query <@ seq { 
            for day in days do
                for sq in db.SecurityQuote do
                    if sq.SecurityId =?! securityId && sq.Day = day then
                        yield sq ;
        } @>

And a List.exists clause:

let getHistoricalQuotes (securityId:int) (days:string list) =
    let results = 
        Query.query <@ seq { 
            for sq in db.SecurityQuote do
                if sq.SecurityId =?! securityId && List.exists (fun d -> d = sq.Day) days then
                    yield sq ;
        } @> 

    results

Both are giving me:

base {System.SystemException} = {"Method 'Microsoft.FSharp.Core.FSharpFunc2[System.String,System.Boolean] ToFSharpFunc[String,Boolean](System.Converter2[System.String,System.Boolean])' has no supported translation to SQL."}

as always, thanks for the help in advance...

rewrote to:

let getHistoricalQuotes (securityId:int) (days:string list) =
    let results = 
        Query.query <@ 
            Query.join
                db.SecurityQuote 
                days
                (fun sq -> if(sq.SecurityId =?! securityId) then sq.Day else "") 
                (fun d -> d) 
                (fun sq d -> sq)  @> 

    results

and got this excepion:

System.Exception was unhandled
Message=The following construct was used in query but is not recognised by the F#-to-LINQ query translator: Value (["2010/01/04"; "2010/02/01"; "2010/03/01"; "2010/04/01"; "2010/05/03"; "2010/06/01"; "2010/07/01"; "2010/08/02"; "2010/09/01"; "2010/10/01"; "2010/11/01"; "2010/12/01"; "2010/01/29"; "2010/02/26"; "2010/03/31"; "2010/04/30"; "2010/05/31"; "2010/06/30"; "2010/07/30"; "2010/08/31"; "2010/09/30"; "2010/10/29"; "2010/11/30"; "2010/12/31"]) This is not a valid query expression. Check the specification of permitted queries and consider moving some of the query out of the quotation

and removing the quotations didn't cause an error - but it generated a incredibly incorrect query (selext * from historicalquote)

Cheating for now, until I can come back and fix the problem - but at least I can keep the signature the same, though I will be thrashing the database.

let getHistoricalQuotes (securityId:int) (days:string list) =
    let getQuote (day) =  
        Query.query <@ seq { 
            for sq in db.SecurityQuote do
                if sq.SecurityId =?! securityId && sq.Day = day then
                    yield sq ;
        } @> |> Seq.head

    List.map (fun day -> getQuote day) days

Per Will below i tried this

let getHistoricalQuotes (securityId:int) (days:string list) =
    let results =  
        Query.query <@ seq { 
            for sq in db.SecurityQuote do
                if sq.SecurityId =?! securityId && days.Contains(sq.Day) then
                    yield sq ;
        } @> 
    results

But it doesn't compile

Error 1 The field, constructor or member 'Contains' is not defined

Finally - (Thank you will):

let getHistoricalQuotes (securityId:int) (days:string array) =
    let results =  
        Query.query <@ seq { 
            for sq in db.SecurityQuote do
                if sq.SecurityId =?! securityId && days.Contains(sq.Day) then
                    yield sq ;
        } @> 
    results

And yes - you need to open System.Linq - Are those extension methods, or at elast partially - they must be,

I just read somewhere that F# might not support consuming extension methods... I'm looking for an alternative... http://stackoverflow.com/questions/777247/using-extension-methods-defined-in-c-from-f-code — Will, Nov 05 '10 at 21:58
Try System.Linq.Enumerable.Contains(days, day) .. assuming F# supports consuming generic methods.. — Will, Nov 05 '10 at 22:03
Glad to have helped, and good to see that F# does support extensions - they're nice syntactic sugar. With `open System.Linq` in place, check out the other methods hanging off any IEnumerable object - there's alot to play with, and most work with EF. Cheers — Will, Nov 09 '10 at 04:30

score 2 · Accepted Answer · answered Nov 05 '10 at 20:59

2

It already exists in Linq, but the opposite of what you're use to:

days.Contains(day)

answered Nov 05 '10 at 20:59

Will

2,512
14
19

It doesn't compile for me. 'Contains' is not defined. I must be putting it in the wrong place. I am editing above... – akaphenom Nov 05 '10 at 21:09
I'm a C# developer, and can barely read F#, but Contains is part of the .Net framework so should work across all languages. I normally write code similar to this: `var validValues = new[] {1,2,3}; var result = source.Where(item => validValues.Contains(item.Property1));` Hopefully you can read C# and translate... – Will Nov 05 '10 at 21:14
I take it you're importing/using System.Linq? (or whatever the equivalent statement is in F#) – Will Nov 05 '10 at 21:16
Ah, "open System.Linq" ... you'll make an F# developer of me yet ;) – Will Nov 05 '10 at 21:56

FLinq SQL "IN Clause"

1 Answers1