f#查询表达式通过列表中的Id获取元素

Question

我使用 f# 从 csv 文件更新数据库，所以我使用 Fsharp.Data解析它，它按预期运行良好，然后 SqlProvider进行实际更新。
所以我得到了一个 list我的 csv 中的项目之一，其中一个字段是我要更新的表的标识符。我来自 c# 背景，所以使用 LINQ我应该做这样的事情:

var results = context.MyTable.Where(m => myList.Contains(m.Identifier))

这会给我我正在寻找的行。我试图这样做

query {
    for m in ctx.Dbo.MyTable do
    where (List.contains m.Identifier myList)
    select m
}

并在运行时出现此错误:

System.InvalidOperationException: variable 'm' of type 'FSharp.Data.Sql.Common.SqlEntity' referenced from scope '', but it is not defined
at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e)
at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence)
at Microsoft.FSharp.Linq.QueryModule.clo@1727-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Execute[a,b](FSharpExpr`1 q)
at .$FSI_0006.main@()
Stopped due to error

然后尝试了以下

query {
    for m in ctx.Dbo.MyTable do
    for id in myList do
    where (id = m.Identifier)
    select m
}

并在编译时得到这个:

myScript.fsx(291,30): error FS0001: The type 'string list' is not compatible with the type 'System.Linq.IQueryable'

我确定这可以做到，我想知道我做错了什么

解决这个问题的正确方法是什么？
我认为这显然是错误的是什么？

更新

我只是按照 Aaron 的回答，这就是我的代码现在的样子，至少是相关部分:

let ctx = Sql.GetDataContext();

let getZipCodes (zipcodes: string list) = 
    query {
        for zc in ctx.CleanZipCodes do
        for id in zipcodes do
        where (id = zc.ZipCode)
        select zc
    }
    |> Seq.toList

现在我收到这个错误:

System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. --->

System.Data.SqlClient.SqlException: Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action'1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource'1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

--- End of inner exception stack trace ---

at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
at System.Data.Objects.ObjectQuery'1.GetResults(Nullable'1 forMergeOption) at System.Data.Objects.ObjectQuery'1.System.Collections.Generic.IEnumerable.GetEnumerator()
at Microsoft.FSharp.Collections.SeqModule.ToList[T](IEnumerable'1 source)
at .$FSI_0006.main@()

Answer 1

您第二个示例中的错误似乎是 LINQ to SQL 的问题。您可以使用名为 SqlEntityConnection 的 SQL 实体类型提供程序来解决此问题。在内置类型提供程序中:

#r "System.Data.Entity"
#r "FSharp.Data.TypeProviders"

open Microsoft.FSharp.Data.TypeProviders

type SqlDb = SqlEntityConnection<"...">

let context = SqlDb.GetDataContext()

let myList = [...]

query {
    for record in context.MyTable do
    for id in myList do
    where (id = record.Identifier)
    select record
} |> Seq.toList