f# - FLinq SQL "IN Clause"

Question

我正在尝试通过 f# linq 不幸地创建一个“IN CLause” - 但似乎无法得到它。我尝试了两种选择:

作为序列表达式的一部分迭代天数列表:

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 ;
        } @> 

还有一个 List.exists 子句:

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

两者都给了我:

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."}

一如既往，提前感谢您的帮助...

<小时/>

重写为:

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

并得到这个异常:

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

删除引号不会导致错误 - 但它生成了一个极其错误的查询(selext * from Historicalquote)

<小时/>

暂时作弊，直到我回来解决问题 - 但至少我可以保持签名相同，尽管我会破坏数据库。

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

<小时/>

根据下面的遗嘱，我尝试了这个

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

但是无法编译

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

<小时/>

最后 - (谢谢你):

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

是的 - 您需要打开 System.Linq - 这些扩展方法是，或者至少部分 - 它们必须是，

Answer 1

它已经存在于 Linq 中，但与您习惯的相反:

days.Contains(day)