f# - FSharp.Data.TypeProviders SQLDataConnection

Question

我已经使用FSharp.Data.TypeProvider多次，但这是我第一次遇到此错误。我能够毫无问题地连接到SQL数据库，并且还可以运行查询，但是当我尝试使用任何Seq时。函数(例如|> Seq.toArray)，出现超时过期错误。

type dbSchema = SqlDataConnection<DBString, Views = false, Functions = false, StoredProcedures = false>
let db = dbSchema.GetDataContext()

返回:

type dbSchema =
  class
    static member GetDataContext : unit -> edbSchema.ServiceTypes.SimpleDataContextTypes.dbTableOutput
     + 1 overload
    nested type ServiceTypes
  end

然后我运行一个简单的查询:

let query1 = 
  let q = query { for a in db.Products do
                  select (a.Date,a.PId, a.Tax)} 
  q |> Seq.map (fun (a,b,c) -> (a,b,c)) 

返回:

val query1: seq<DateTime * Nullable<int> * float>

现在，如果我尝试运行一些简单的内容，例如:

query1 |> Seq.head

我收到以下错误:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   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.TryCloseInternal(Boolean closeReader)
   at System.Data.SqlClient.SqlDataReader.Close()
   at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReaderSession`1.Dispose()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.Dispose()
   at Microsoft.FSharp.Collections.SeqModule.Head[T](IEnumerable`1 source)
   at <StartupCode$FSI_0007>.$FSI_0007.main@()
ClientConnectionId:6b4036ff-6ef4-4224-ad7a-08f8b4808b1b
Stopped due to error

我将不胜感激任何帮助。

谢谢

我找到了这个:

When you use a query expression, you must remember that the query is subject to lazy evaluation. Therefore, the database is still open for reading during any chained evaluations, such as in the lambda expression blocks after each query expression. Any database operation that explicitly or implicitly uses a transaction must occur after the read operations have completed.

无论如何，是否有执行查询且不受懒惰评估的影响？

我认为可能有一种使用完整数据上下文和executionquery来实现此目标的方法，但是您却失去了类型提供程序的大部分好处

Answer 1

除了将查询强制到列表之外，您还可以在连接字符串中指定超时，例如Connection Timeout = 60。您还可以尝试使用datacontext进行其他操作，例如:

db.DataContext.ObjectTrackingEnabled <- false  
db.DataContext.CommandTimeout <- 90

但是，在许多情况下，可以在数据库端更好地解决这些类型的超时问题，而您提到这是无法做到的。我的大多数超时问题都是通过添加索引解决的。因此，您可能需要对查询进行性能分析。